subs_voyagu_tasks

--TASK 1
with test as (
SELECT
concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date)) as date
, license_name
, count(license_id) as subscriptions
FROM `test-project-369620.voyagu.subscriptions`
where operation_type = 'purchase'
group by
concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date))
, license_name
)
select *
, sum(subscriptions) over(partition by license_name order by date rows between unbounded preceding and current row) as subscription_summary
from test
order by
date
, license_name


--TASK 2
Select
first
, (licenses)
, round((SUM(CASE WHEN month_number = 0 THEN 1 ELSE 0 END) / licenses), 2) AS month_0
, round((SUM(CASE WHEN month_number = 1 THEN 1 ELSE 0 END) / licenses), 2) AS month_1
, round((SUM(CASE WHEN month_number = 2 THEN 1 ELSE 0 END) / licenses), 2) AS month_2
, round((SUM(CASE WHEN month_number = 3 THEN 1 ELSE 0 END) / licenses), 2) AS month_3
, round((SUM(CASE WHEN month_number = 4 THEN 1 ELSE 0 END) / licenses), 2) AS month_4
, round((SUM(CASE WHEN month_number = 5 THEN 1 ELSE 0 END) / licenses), 2) AS month_5
, round((SUM(CASE WHEN month_number = 6 THEN 1 ELSE 0 END) / licenses), 2) AS month_6
from
(select
m.license_id
, m.dates
, n.first as first
, DATE_DIFF(dates, first, month) as month_number
from
(SELECT
license_id
, cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date) as dates
FROM `test-project-369620.voyagu.subscriptions`
where operation_type = 'purchase'
and billing_period = 'month'
GROUP BY
license_id
,cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date)
) m
, (SELECT
license_id
, cast((min(concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01"))) as date) AS first
FROM `test-project-369620.voyagu.subscriptions`
where operation_type = 'purchase'
and billing_period = 'month'
GROUP BY
license_id
) n
where m.license_id = n.license_id
) as with_week_number
left join (select
cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date) as date
, count(distinct license_id) as licenses
FROM `test-project-369620.voyagu.subscriptions`
where operation_type = 'purchase'
and billing_period = 'month'
group by cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date)
) all_sales on all_sales.date = with_week_number.first
group by first , licenses
order by first