Share of plays cut short

conditional logic

Share of plays cut short

Pandora Pandas Interview Question

A Pandora listener who skips most of what they are served is being recommended the wrong music, and they churn long before they complain. The signal is a play that ends early: anything under three minutes counts as cut short.

Using the plays DataFrame, find each listener_id, their number of plays as plays_total, and the percentage of those cut short as short_pct, rounded to 1 decimal place. Sort by short_pct, highest first. Assign the answer to result.

Asked of

  • Data Analyst
  • Business Analyst
  • BI Analyst
  • Product Analyst
  • Analytics Engineer
  • Data Scientist

playsDataFrame30 rows

Column NameType
play_idint64
listener_idstr
track_idstr
played_atstr
ms_playedint64

playsExample Input

play_idlistener_idtrack_idplayed_atms_played
5009L02T1002024-03-04 07:30:00120000
5010L02T1212024-03-04 07:34:00250000
5011L02T1212024-03-04 07:39:00248000
5012L02T1212024-03-04 07:45:00251000
5013L02T1042024-03-04 12:05:0090000
5014L02T1182024-03-04 12:12:00215000
5015L02T1212024-03-04 21:02:00244000
5016L02T1002024-03-04 21:08:00118000
5026L04T1002024-03-04 11:00:00204000
5027L04T1002024-03-04 11:05:00201000
5028L04T1042024-03-04 15:45:00178000
5029L04T1182024-03-04 15:52:00162000
5030L04T1212024-03-04 15:58:00249000

Example Output

listener_idplays_totalshort_pct
L04540
L02837.5

Explanation

L04 played five tracks and two of them stopped before three minutes, which is 40 percent. L02 played eight and three were cut short, which is 37.5 percent, so L04 sorts first.

The example above is a small slice of the data. Your code runs against the full DataFrames.