I have the following task: Write a query to get the max gap between active dates for each user in the following table: (e.g, user 1 will have max. gap of 3 days, user 2 will have 30 days, and user 3 will have null.)
CREATE TABLE IF NOT EXISTS `gamers` (
`id` int(6) not null,
`user_id` int(3) not null,
`active_date` DATE Not null,
PRIMARY KEY (`id`)
);
INSERT INTO `gamers` (`id`,`user_id`,`active_date`) VALUES
('1','1','2019-01-01'),
('2','1','2019-01-02'),
('3','1','2019-01-05'),
('4','2','2019-03-01'),
('5','2','2019-03-31'),
('6','3','2019-04-01');
My solution would be the following:
SELECT g.id as id, g.user_id, MAX(total_amount_spent) OVER(PARTITION BY g.country), g.country
FROM gamers2 as g
INNER JOIN cte as c
ON c.country = g.country
WHERE install_source = 'ua' AND g.id NOT IN (SELECT id FROM cte)
GROUP BY g.country
ORDER BY g.user_id, total_amount_spent DESC
),
cte3 AS(
SELECT g.id, g.user_id, MAX(total_amount_spent) OVER(PARTITION BY g.country), g.country
FROM gamers2 as g
INNER JOIN cte2 as c
ON c.country = g.country
WHERE install_source = 'ua' AND g.id NOT IN (SELECT id FROM cte2)
GROUP BY g.country
ORDER BY g.user_id, total_amount_spent DESC
)
SELECT * FROM cte
UNION
SELECT * FROM cte2
UNION
SELECT * FROM cte3
CodePudding user response:
We can use LAG()
combined with ROW_NUMBER()
as follows:
WITH cte AS (
SELECT *, LAG(active_date, 1, active_date) OVER
(PARTITION BY user_id ORDER BY active_date) lag_active_date
FROM gamers2
),
cte2 AS (
SELECT *, DATEDIFF(active_date, lag_active_date) AS diff,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY DATEDIFF(active_date, lag_active_date) DESC) AS rn
FROM cte
)
SELECT user_id, diff
FROM cte2
WHERE rn = 1;
CodePudding user response:
A bit more readable, in my opinion:
WITH
w_lag_val AS (
SELECT
id
, user_id
, active_date
, active_date
- LAG(active_date) OVER(
PARTITION BY user_id ORDER BY active_date
)
AS lag_val
FROM gamers
)
SELECT
user_id
, MAX(lag_val) AS max_gap
FROM w_lag_val
GROUP BY user_id
ORDER BY user_id
;
-- out user_id | max_gap
-- out --------- ---------
-- out 1 | 3
-- out 2 | 30
-- out 3 | (null)
CodePudding user response:
select *, max(diff) from (
select *, DATEDIFF( f,active_date)as diff from (
SELECT gamers.id , gamers.user_id, gamers.active_date,
(select active_date from gamers as g1 where g1.active_date > gamers.active_date and gamers.user_id = g1.user_id order by g1.active_date limit 1) as f
from gamers
order by gamers.id) as vv) as gg
group by user_id
there is another solution