Join Tables and Rank Users

You are given two analytics tables:

• users(user_id, country, signup_date)
• listening_events(event_id, user_id, played_at, minutes_played)

Because the data pipeline may retry writes, listening_events can contain duplicate rows with the same event_id.

Write a SQL query to return, for each country, the top 3 users by total minutes_played in the last 30 days. For each returned row, include country, user_id, total_minutes, and the user's rank within the country.

Then discuss:

1. How you would deduplicate events before aggregation.
2. How you would optimize the query if listening_events is extremely large.
3. When partitioning versus sharding would help.