Aysat
46 lines
1.0 KiB
SQL
46 lines
1.0 KiB
SQL
'''
|
|
Задача 1.
|
|
|
|
Таблица users
|
|
|
|
| user_id | date |
|
|
| --- | --- |
|
|
| 1 | 2018-07-01 |
|
|
| 234 | 2018-07-02 |
|
|
| 3 | 2018-07-02 |
|
|
| 1 | 2018-07-02 |
|
|
| ... | ... |
|
|
| 234 | 2018-10-04 |
|
|
|
|
Нужно найти ежемесячное процентное изменение месячной аудитории активных пользователей.
|
|
'''
|
|
|
|
with monthly_users as (
|
|
select
|
|
count(distinct(user_id)) as active_users,
|
|
date_trunc('month', date) as month
|
|
from
|
|
abakhytbekuly.users
|
|
group by
|
|
date_trunc('month', date)
|
|
),
|
|
change as (
|
|
select active_users,
|
|
month,
|
|
lag(active_users) over(order by month) as prev_mau
|
|
from
|
|
monthly_users
|
|
)
|
|
select
|
|
active_users,
|
|
month,
|
|
prev_mau,
|
|
100.0 * (active_users - prev_mau) / prev_mau as pct_change -- тут результат странный типа '-66.666666'
|
|
from
|
|
change
|
|
where
|
|
prev_mau is not null
|
|
order by
|
|
month
|
|
|