SQL Coding : Solved on BigQuery, visualised on Google Data Studio

I was provided two datasets. I took the datasets. Firstly, I have joined two sheets, here is the code below:

 

First I have joined two sheets, here is the code below:

select *
from
`vigilant-shift-352303.10ms.rerollments`  AS reroll
join `vigilant-shift-352303.10ms.courselist`  AS clist

on reroll.course_id = clist.course_id

 

The first problem asked to find the month on month comparison of paid vs  users unique counts.  

Here is the code: 

 

Select paid_box.paid_users, paid_box.month,free_box.free_users
from

(SELECT count(joined_new.user_id) as paid_users, datetime_trunc(joined_new.enroll_date, month) as month
from
(select *
from
`vigilant-shift-352303.10ms.rerollments`  AS reroll
join `vigilant-shift-352303.10ms.courselist`  AS clist

on reroll.course_id = clist.course_id
where vertical = 'Paid') as joined_new

group by datetime_trunc(joined_new.enroll_date, month),datetime_trunc(joined_new.enroll_date, year)) as paid_box

join (SELECT count(joined_new.user_id) as free_users, datetime_trunc(joined_new.enroll_date, month) as month
from
(select *
from
`vigilant-shift-352303.10ms.rerollments`  AS reroll
join `vigilant-shift-352303.10ms.courselist`  AS clist

on reroll.course_id = clist.course_id
where vertical = 'Free') as joined_new

group by datetime_trunc(joined_new.enroll_date, month),datetime_trunc(joined_new.enroll_date, year)) as free_box
on paid_box.month=free_box.month;

 

At the second stage of the solution, we wanted to  we want to see the free to paid monthly conversion rate. Here is the code:

Select date_trunc(P1.date, month) as month, count(distinct P1.user) as users

from

(select f.enroll_date as date,f.user_id as user, f.Vertical as scheme
from
`vigilant-shift-352303.10ms.joined10ms` as f
where Vertical = 'Free') as f1

Inner join
(select p.enroll_date as date,p.user_id as user, p.Vertical as scheme
from
`vigilant-shift-352303.10ms.joined10ms` as p
where Vertical = 'Paid') as P1
on f1.user = P1.user

group by date_trunc(P1.date, month);

 

 

Lastly, we wanted to solve the last problem which is finding the paid to paid month on month retention counts. Here is the code: 

 

For your problem Number 3:

Select count(distinct m.user) as usercount, date_trunc(m.date,month) as month,

from
(select n.user_id as user, rank() over (partition by n.user_id order by n.enroll_date) as rn, n.enroll_date as date
from
`vigilant-shift-352303.10ms.joined10ms` as n
where n.vertical = 'Paid') as m
where rn > 1

group by date_trunc(m.date,month);

 

I have solved on BigQuery and later visualized on Google Data Studio. Yes, I know I could make the visualization more interactive but maybe that is for other time. 

Here is the link for the visualisation:DataStudio

Here is the bigQuery Project link: BigQuery Project