Listener Engagement Profile on a Music Service

Problem

A music-streaming service stores listening sessions in `plays` and track metadata in `tracks`.

`plays` has one row per session: `play_id`, the `listener_id`, the `track_id` that was played, the `played_on` date, and the `seconds_listened` for that session. `tracks` has one row per track: `track_id`, its `genre`, and its `duration` in seconds.

For every listener, build an engagement profile with these columns: `listener_id`; `total_seconds` — the sum of seconds_listened across all their sessions, rounded to 2 decimals; `play_count` — how many sessions they have; `unique_genres` — the number of distinct genres they have listened to; `avg_seconds` — their average seconds_listened per session, rounded to 2 decimals; `top_genre` — the genre they played most often (count of sessions in that genre), breaking ties by the genre whose most recent session is later; and `engagement_score`, defined as (play_count * 10) + (total_seconds / 100), rounded to 2 decimals.

Order the result by `engagement_score` descending, then by `listener_id` ascending.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns listener_id, total_seconds, play_count, unique_genres, avg_seconds, top_genre, engagement_score.

Starter code (SQL)

SELECT *
FROM plays;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium