CODEWITHSUNDEEP
sqldatecount
Wenyi Yan
Wenyi Yan

Reputation: 85

SQL monthly subscription rate

How to write a concise sql to get subscription rate by month.

formula: subscription rate = subscription count/ trial count

NOTE: The tricky part is the subscription event should be attributed to the month that company started the trail.

| id    | date       | type  |
|-------|------------|-------|
| 10001 | 2019-01-01 | Trial |
| 10001 | 2019-01-15 | Sub   |
| 10002 | 2019-01-20 | Trial |
| 10002 | 2019-02-10 | Sub   |
| 10003 | 2019-01-01 | Trial |
| 10004 | 2019-02-10 | Trial |


Based on the above table, the out output should be:
2019-01-01  2/3
2019-02-01  0/1

Upvotes: 1

Views: 404

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

You can do this with window functions. Assuming that there are not duplicate trial/subs:

select date_trunc('month', date) as yyyymm,
       count(*) where (num_subs > 0) * 1.0 / count(*)
from (select t.*, 
             count(*) filter (where type = 'Sub') over (partition by id) as num_subs
      from t
     ) t
where type = 'Trial'
group by yyyymm;

If an id can have duplicate trials or subs, then I suggest that you ask a new question with more detail about the duplicates.

You an also do this with two levels of aggregation:

select trial_date, 
       count(sub_date) * 1.0 / count(*)
from (select id, min(date) filter (where type = 'trial') as trial_date,
             min(date) filter (where type = 'sub') as sub_date
      from t
      group by id
     ) id
group by trial_date;

Upvotes: 0

GMB
GMB

Reputation: 222602

One option is a self-join to identify whether each trial eventually subscribed, then aggregation and arithmetics:

select 
    date_trunc('month', t.date) date_month
    1.0 * count(s.id) / count(t.id) rate
from mytable t
left join mytable s on s.id = t.id and s.type = 'Sub'
where t.type = 'Trial'
group by date_trunc('month', t.date)

The syntax to truncate a date to the beginning of the month widely varies across databases. The above would work in Postgres. Alternatives are available in other databases, such as:

date_format(t.date, '%Y-%m-01')               -- MySQL
trunc(t.date, 'mm')                           -- Oracle
datefromparts(year(t.date), month(t.date), 1) -- SQL Server

Upvotes: 2

Related Questions