users age activity report

Problem

We need to build a report that will represent how users with different subscription age are being engaged with our service. We will focus on clicks as a main criteria for us to understand any possible correlations between these two metrics.

Insert data from CSV file

clickhouse-client --host=127.0.0.1 --port=9000 --user=default --database=statistics --query "INSERT INTO subscribers_subscriber FORMAT CSVWithNames" < /tmp/15050_subscribers.csv

Make a query and export the results

SELECT dateDiff('day', ss.subscribed_date, sa.date) as age, sum(sa.clicks) FROM (
    SELECT toDate(timestamp) as date, subscriber_id, sum(clicked_count) as clicks FROM subscribers_activities WHERE date >= '2024-07-01' and clicked_count > 0 and channel_id = 15050 group by subscriber_id, date
) sa JOIN subscribers_subscriber ss ON sa.subscriber_id = ss.id GROUP BY age ORDER BY age INTO OUTFILE '/tmp/15050_age_clicks.csv' FORMAT CSVWithNames;