How to show 5 most recent events grouped by ID

Hello!

I'm trying to create a table that will show the 5 most recent events for each ID in a list.

The dataset consists of a list of QA scores for a group of people and the dates the scores were assigned. I want the table to display only the 5 most recent scores for each separate person. I'm having trouble figuring out how to assign (and reassign) index values to the completed date that are grouped by the person.

Sorry if I'm repeating myself, but I'm trying to add clarity. I want each ID in the list to show 5 scores and dates. I want those 5 scores and dates to be the 5 that were most recently assigned to that ID when the dashboard is being viewed.

my data looks like this:

Can someone please point me to some resources that can help me do this?

Hi! Unfortunately I don't think there's an easy way to do this from the query builder.

I can help you with a SQL workaround, if you want to try that. That would be the fastest way to handle all of the IDs at once.

Otherwise if there's just a handful if IDs that you're interested in, you could create a separate question for each one with the top 5 recent scores and dates.

Hello! Thank you for responding. I would really appreciate it if you could help me with the SQL for this. I need to use all IDs at once.

That would be really helpful.

Hey @nllho I'm hoping you've seen this. Could you please help me with the SQL query or point me to a resource that could help?
Thanks again!

@KHill Hi! Sorry, I was out on vacation -- let me put something together for you today :slight_smile:

You will need to use a window function that works with your SQL dialect.

For the rows/columns you have in your screenshot, the SQL would be something like this:

WITH ranked_by_date AS (
SELECT 
id, 
completed_date, 
qa_score
RANK() OVER (PARTITION BY id ORDER BY completed_date DESC) AS rank
FROM your_database
)

SELECT 
id,
completed_date, 
qa_score
FROM ranked_by_date
WHERE rank BETWEEN 1 AND 5

The ranked_by_date CTE should give you a result like this:

ID Completed Date QA Score Rank
1 2022-01-10 90 1
1 2022-01-09 90 2
1 2022-01-08 90 3
1 2022-01-07 90 4
1 2022-01-06 90 5
1 2022-01-05 90 6
2 2022-01-10 90 1
2 2022-01-09 90 2
2 2022-01-08 90 3
2 2022-01-07 90 4
2 2022-01-06 90 5
2 2022-01-05 90 6

The final SELECT statement will filter for ranks that are 1-5 (that is, the 5 most recent records per ID).

It is definitely a more complex query, but hope this helps!

@nllho I sincerely appreciate your help. I'll try this. I hope your vacation was stellar!

1 Like