знову SQL

[malyj_gorgan]

А ось ще задачка. Трохи спрощений варіант реальної задачі, яку мені треба було розв'язати для одного з наших клієнтів, коли я лише почав працювати на попередній роботі. Там рішення, ніби, існувало, мій попередник на тій позиції написав SELECT query на кілька екранів, з усякими зайвими UNION'ами і неоптимальними ходами, яке в якийсь момент почало ламатися. Я втикав у той код, втикав... плюнув і переписав начисто, коротко і читаблеьно.

А потім я давав таку спрощену задачку на співбесідах, на пізніших стадіях, де ти годину балакаєш наживо. Троє людей її провалили, а дві кандидатки відповіли, хоч і одна і не довела до кінця. Обидвох ми прийняли працювати, обидві стали прекрасними спеціалістами, ліпшими за мене.

 

Словом, ось задача, про відтік юзерів у мобільного оператора:

 

Plot the daily user attrition rate in a mobile network operator. fraction of currently active users leaving the company's active base. The attrition rate is independent of the new user engagement, those are two independent processes (which, when combined, define the growth or decline of the company's user base). All you have is a daily imported from the operator table TBL with the user body information for each day in the last month. The three fields in the table tat you need are like this:

uid: unique user identifier, long hexidecimal string

status: user status (a single character "A", "G", "P", "Z", "D")

dt: date of the recorded status

The user status can be:

- "A" for "acive user", paying, in good standing

- "G" for user in "grace period", not active anymore yet not disabled, can be re-activated if s/he pays back

- other characters are for pre-registered users or users at some stage of being deleted, but for various reason for most users we never see those other status values, whereas any user is guaranteed to spend at least seven days in "A" status before suspension and then some time in "G", too, unless reactivating. 

 

In terms of the status, today's attrition value is the count of users who are in status "A" today and "G" tomorrow minus a small trickle of resurrected users, those who are "G" today and "A" tomorrow. Consequently, the attrition rate is the ratio of this number to the total number of users in status "A" as of today.

 

Write a SQL query that would return two columns: the date and the attrition rate on that day.
You may or may not need to use a function DATEADD(datepart, interval, DATE), which adds "interval" dateparts to a date object, for example DATEADD(DAY, -2, CURRENT_DATE) is the day before yesterday.

(Я спочатку спеціально уточнив, що я на той час лише почав був працювати: я тоді не дуже знав, як працюють WINDOW фунцкії, тому боявся їх і писав усе без них (з ними було би ще на порядок простіше). Це я до того, що можна писати з вікном, а можна без вікна, з JOIN'ом.)

 
Апдейт:
На всякий випадок, уточню, що можливі зміни у статусі юзера є не лише між "A" і "G" . Варіантів пар статусів сьогодні-завтра у одного юзера багато, просто не всі завжди видно. Ось повний перелік: NULL-P, NULL-A, P-P, P-A, A-A, A-G, G-A, G-G, G-Z, G-C, G-NULL, C-NULL, Z-NULL. (NULL означає, що нема рядка для даного юзера в даний день)

Update 2: варіанти розв'язку в коментах

Comments

[sassa_nf]

думаю, uid не потрібне, бо state transition гарантує, що G існує лише для користувачів, які колись раніше були A. Тоді достатньо просто порахувати зміни в G: (dG / dt) / A.

Далі треба співставити одну і ту ж таблицю користувачів у стані G, зсунувши дату на 1 день - так порахуємо зміну G за один день, і цей результат знову співставити з таблицею користувачів у стані A. Мені здається, що краще спочатку звести таблицю станів користувачів до зменшеної таблиці з лише двома станами, ніж будувати дві таблиці для кожного стану.

with bydate as (select status, dt, count(status) as c from tbl where status in ('A', 'G') group by dt, status)
with dg as (select (b.c - a.c) as c, a.dt from (select dt, c from bydate where status = 'G') as a inner join (select dateadd(day, -1, dt) as dt, c from bydate where status = 'G') as b on a.dt = b.dt)
select dg.c / a.c, a.dt from (select dt, c from bydate where a.status = 'A') as a inner join dg on a.dt = dg.dt

(можливо має сенс скрінити коменти :))

[malyj_gorgan]

Скрінити коменти облом, а чесність наше все. :)

uid потрібне, тому що після статуса "G" юзер не обов'язково попадаЕ назад в "А", а може зовсім зникнути, або попасти в якийсь із статусів витиральних (вже і не пригадаю, який конкретно був у того провайдера, "D" і "C", здається)

[sassa_nf]

(а, та я не про чесність, а про якщо комусь хочеться самому докумекати)

про uid... гм, так

[sassa_nf]

ну дуже вже не хочеться слідкувати за історією всіх користувачів!

але, скажімо, якщо користувачів в стані G дуже невелика частка від усіх, то можна прослідкувати історію користувачів, які в який небудь день були в стані G. Тоді потрібна частка attrition буде обов'язково серед них.

with history as (select uid, status, dt, dateadd(day, -1, tbl.dt) as prev from tbl where uid in (select distinct uid from tbl where status = 'G') and status in ('A', 'G'))
with transitions as (select d1.status as s1, d2.status as s2, count(*) as c, d1.dt from history as d1 inner join history as d2 on d1.dt = d2.prev and d1.uid = d2.uid where d1.status <> d2.status group by d1.status, d2.status)
with userbase as (select count(*) as c, dt from tbl where status = 'A' group by dt)
...

[malyj_gorgan]

Тут хочеться, не хочеться, а треба. Правда, не за всею історією, а за точкою переходу між A і G. Тим більше, користувачів у стані G, за порядком величини, стільки ж, скільки і А.
Там же сформульовано, що треба порахувати, для кожного дня D:
[ (Кількість юзерів, які були A в день D, але стали G у день (D+1)) мінус (кількість юзерів у статусі G в день D, які стали A в день (D+1) )]
і це все поділити на (загальну кількість юзерів у статусі A станом на день D)

Я сьогодні ввечері відповідь запощу окремим коментом :)

[malyj_gorgan]

А це не від вас часом мені прийшов LinkedIn connect request? Всякі англійські топоніми в анамнезі? Оракл?

[sassa_nf]

:) було!

Розв'язок

[malyj_gorgan]

Нагадую, що задача, для кожного для, порахувати: (Σ_A→G − Σ_G→A) / Σ_A
Тобто, все, чого треба досягнути, це порахувати отакі можливі переходи. Я це робив би двома способами (Якщо ви знаєте концептуально інакший третій спосіб, скажіть, мені цікаво.)

Спосіб 1: INNER JOIN таблиці tbl з собою ж, зміщеною на день назад по даті і uid:
SELECT dt,
  SUM(CASE WHEN s1='A' AND s2='G' THEN 1.
           WHEN s1='G' AND s2='A' THEN -1
           ELSE 0. END) / SUM(CASE WHEN s1=A. THEN 1. ELSE 0. END) AS attr_rate
FROM tbl
     INNER JOIN (SELECT uid, DATEADD(DAY, -1, dt) AS dt, status AS s2 FROM tbl)
     USING(uid, dt)
GROUP BY dt
ORDER BY dt

Спосіб 2: Використати віконну функцію LEAD(), щоби порахувати завтрашній статус.
WITH a AS (
SELECT
  dt,
  status,
  CONCAT(status, LEAD(status) OVER (PARTITION by uid ORDER BY dt)) s
FROM tbl)

SELECT dt,
  SUM(CASE WHEN s='AG' THEN 1. WHEN s='GA' THEN -1. ELSE 0. END
      ) / SUM(CASE WHEN status='A' THEN 1. ELSE 0. END) AS attr_rate
FROM a
GROUP BY dt
ORDER BY dt