WOW growth: Understanding SQL CTE functions with very common analysis
A very important part of being a data analyst is creating SQL reports and interacting with your platform database. In my experience, I have to use SQL every day, sometimes for small data and sometimes to create weekly growth reports.
A week on Week growth tracking is ideal for a SaaS platform. The report is basically tracking your metrics which can be the number of active users or the number of users completing the signup on weekly basis.
A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects
It becomes more important and relevant in this scenario where an Agile sprint also runs for typical 1–2 weeks. Hence any product/feature release impact on these metrics can also be observed on weekly basis.
People familiar with windows and CTE functions can start using it right away:
with weekly_time_series(report_date) AS (
select generate_series( ‘2022–01–01’, current_date, interval ‘1 day’ )
), weekly_counts(report_week, weekly_count) as (
select
date_trunc(‘week’, report_date) as week,
count(*) as total_users_joined
from weekly_time_series
left join data_table d
on date_trunc(‘day’, created_at) <= report_date
group by 1
)
select report_week
, weekly_count,
round((lead(weekly_count) over (order by report_week)-weekly_count)*100.0/(weekly_count),2)
as growthfrom weekly_counts
Those who are still starting please go through the following explanation so that you can mould it in your way.
First, with the table generate the dates from the start of the year to the current date by an interval of one day.
Second, CTE is extracting a week from each date and counting users who are active on the date. By grouping, we are aggregating for the week extracted.
In the last step, we are calculating the weekly growth change. The lead function is basically from getting the leading weekly count.
Thanks! :)
