Компания определяет своих суперпользователей как тех, кто совершил не менее двух транзакций. Из следующей таблицы (см картинку) напишите запрос, чтобы вернуть для каждого пользователя дату, когда он стал суперпользователем, сначала тех, кто стал раньше суперпользователем. Пользователи, которые не являются суперпользователями, также должны присутствовать в таблице.
Решение
WITH users (user_id, action, action_date)
AS (VALUES
(1, 'start', CAST('2-12-20' AS date)),
(1, 'cancel', CAST('2-13-20' AS date)),
(2, 'start', CAST('2-11-20' AS date)),
(2, 'publish', CAST('2-14-20' AS date)),
(3, 'start', CAST('2-15-20' AS date)),
(3, 'cancel', CAST('2-15-20' AS date)),
(4, 'start', CAST('2-18-20' AS date)),
(1, 'publish', CAST('2-19-20' AS date))),
-- create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function
t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),
-- filter on date rank column to pull latest and next latest actions from this table
latest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),
next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )
-- left join these two tables, subtracting latest from second latest to get time elapsed
SELECT
l1.user_id,
l1.action_date - l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1@sqlhub