Using SQL to get recent n days activity of every user in the table

Question

I have a table of game activity of users which looks something like this

So,for simplicity just consider account_id and date column. By now you might've understood that each record represents a player playing some game on a specific day. What I want to extract is the recent 15 days activity of every user counting backwards from his last game played. For eg,we have data that ranges from 4th April 2020 to 24 Sep 2020, Let's just say that a user played his last game on 20th Sep 2020 and hasn't played any game since then, so for that user I want his playing activity for the date ranging from 5th to 20th Sep(15 day back from his last game played) and I want to extract the same data for every user.

I've initially thought to implement this like ..... sort the table in descending order based on date and match the date with that specific account when that account_id appears for the first time(to create a dictionary in which key is account_id and value is last date he played) so that I can subtract 15 days from the value and filter the data for every account_id, but my co-worker isn't satisfied with that and is expecting to do all this within a single shot(using SQL query). Can someone guide me on how to do that. Thanks in advance :)

Answer 1

If I understood this correctly, you are basically looking for the MAX(Date) Grouped BY User as your starting (actually end) point.

It's easiest to put this in a subquery or CTE.

Then you can simply query your table again using the last date by user as your end date and calculate that date - 15 days as your start point.

This will retrieve all entries for users in the given period.

Example:

WITH BASE AS( 
SELECT 
MAX(Date) AS LastDate, 
UserID 
FROM GameActivity 
GROUP BY UserID
) 
SELECT 
ga.UserID, 
ga.Date
FROM GameActivity GA 
JOIN BASE B ON b.UserID = ga.UserID 
WHERE ga.Date >= DATE_SUB(b.LastDate, INTERVAL 15 DAY) 
  AND ga.Date <= b.LastDate  

EDIT:

For getting the last 15 days regardless of actual dates, I would personally use a Window Function to count back I split this into 2 CTEs to highlight the logic

WITH DistinctDates AS (
SELECT DISTINCT 
user_id, 
active_date
  
FROM userdata
), 

DAYCount AS ( 
SELECT 
user_id, 
active_date, 
COUNT(active_date) OVER (PARTITION BY user_id ORDER BY active_date DESC) AS ActiveDays

FROM DistinctDates 
) 

SELECT 
dc.user_id, 
ud.active_date,
dc.ActiveDays 

FROM DayCount DC
JOIN userdata UD ON ud.user_id = dc.user_id AND ud.active_date = dc.active_date

WHERE ActiveDays BETWEEN 1 AND 15 

ORDER BY dc.user_id, dc.ActiveDays ; 

I tried this on MS SQL Server but MySQL should work the same