CODEWITHSUNDEEP
sqlpostgresql
ilmoi
ilmoi

Reputation: 2534

SQL - how to build cohort analysis

Ok so right now I'm pulling data from SQL and it looks like this:

Cohort     Date     Revenue
---------------------------
Dec 16    Dec 16    30.00  
Dec 16    Jan 17    31.00  
Dec 16    Feb 17    32.00  
Jan 17    Jan 17    40.00  
Jan 17    Feb 17    41.00  
Feb 18    Feb 17    50.00

What I want is this:

Cohort  |           Date           
        | Month0 | Month1 | Month2 
--------|--------|--------|--------
Dec 16  | 30.00  | 31.00  | 32.00  
Jan 17  | 40.00  | 41.00  |
Feb 18  | 50.00  |        |

Right now I go into excel and do SUMPRODUCT to convert it - which takes resources and time.

Is there a way to get second view out of SQL straight away? Assume I'm SQL 101 please - I've been doing it for a month.

Upvotes: 1

Views: 2125

Answers (2)

light souls
light souls

Reputation: 728

You can also use crosstab function to create a pivot table. Before using these example you need to install the extension

CREATE EXTENSION tablefunc

After that you can execute this query. To achieve dec 16 comes first i have sorted beginning with year-month-day:

select *
from crosstab(
$$select t.cohort,
         t.date,
         sum(t.revenue)
  from your_table t
  group by t.cohort,t.date
  order by to_char(to_date('01-'||t.cohort,'dd-Mon-yy'),'YYYYMMDD'),
           to_char(to_date('01-'||t.date,'dd-Mon-yy'),'YYYYMMDD') asc
$$) 
as months(cohort text,Month0 NUMERIC,Month1 NUMERIC,Month2 NUMERIC)

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use window function dense_rank and then use aggregation to pivot the table.

select cohort,
    sum(case when rn = 1 then Revenue end) Month0,
    sum(case when rn = 2 then Revenue end) Month1,
    sum(case when rn = 3 then Revenue end) Month2
from (
    select t.*,
        dense_rank() over (
            partition by cohort order by to_date('01-' || date, 'dd-Mon-yy')
            ) rn
    from your_table t
    ) t
group by cohort

Upvotes: 2

Related Questions