Katkat
Back

Find the Share of Power Listeners per Region from Play Events

PinterestData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Write a PostgreSQL query that, for a trailing 7-day window, classifies listeners per region as active or 'power' users based on how many distinct days they listened and whether they used at least three different playback devices on any single one of those days, then reports the power-user share per region.

A music app logs every playback as a row in play_events:

ColumnMeaning
event_idIdentifier of the playback event
user_idListener who triggered it
track_idTrack played
devicePlayback surface: app, web, or car
event_tsTimestamp of the event
region_codeListener's region at play time

Look only at the trailing 7-calendar-day window: today and the 6 days before it, using the date portion of event_ts. Within a region, a listener is active if they have at least one play event in the window. A listener is a power listener if, within that same window, they played on at least 4 distinct calendar days, AND there exists some single day among those plays where they used 3 or more distinct devices. That device-diversity requirement has to be met within one day's worth of plays — tallying up three different devices used across three separate days doesn't satisfy it.

A listener should be evaluated once per region they were active in during the window (the same person could show up as a listener for two regions if they traveled).

Write one read-only PostgreSQL query returning, per region with at least one active listener in the window:

  • region_code
  • active_users — count of active listeners
  • power_users — count of those who are also power listeners
  • power_user_pct — power_users / active_users * 100, rounded to 2 decimal places

Use the same 7-day window for both the numerator and denominator. Include a region even if it has zero power listeners. Sort by power_user_pct descending, then region_code ascending.

Worked example (window: 2026-10-14 through 2026-10-20, "today" = 2026-10-20)

userregiondatedevice
u1US10-16app
u1US10-17app
u1US10-17web
u1US10-18app
u1US10-18web
u1US10-18car
u1US10-19app
u2US10-20app
u3CA10-15app
u3CA10-16app
u3CA10-17app
u3CA10-18app
u3CA10-18web
u3CA10-18car
u4CA10-20app

Expected result: CA (active=2, power=1, pct=50.00) before US (active=2, power=1, pct=50.00), tied on percentage and broken by region name ascending.

Hints

Hint 1

First collapse to one row per (region, user, date, device-count-that-day) with a GROUP BY before you try to evaluate either rule.

Hint 2

The 4-distinct-days rule and the 3-distinct-devices-in-one-day rule operate at different grains — compute them in separate aggregation steps and only combine them at the per-user level.

Hint 3

COUNT(DISTINCT date) and MAX(device_count_per_day) per user are the two numbers you need; everything else is a filter and a final GROUP BY region.

Hint 4

Use integer division carefully — multiply by 100.0 (not 100) before dividing so PostgreSQL doesn't truncate to an integer.

Follow-up questions

  • How would the query change if 'power listener' required the device-diversity condition on at least two separate days, not just one?
  • How would you make the window length and the two thresholds (4 days, 3 devices) parameters instead of hard-coded literals?
  • How would you add a week-over-week trend of power_user_pct per region without re-scanning the full event history for every day?
  • What indexes would you want on play_events to make this query fast at billions of rows?