202. Monthly active paid subscriptions

hard apple

  • **********************************************************************************************************2023.
  • *****************************************************
  • ************************************************************************************************************************
  • *******************************************************************************************************************************************************
  • ************************************************************
  • '******************************************************
  • **************************************************************************************************************************************************************************************************************

Table 1: apple_subscription

Subscription table

     Column      |            Type             
 subscription_id | bigint                      
 product_name    | character varying(100)      
 country         | character varying(2)        
 start_at        | timestamp 
 end_at          | timestamp
 state           | character varying(20)       

Table 2: dates

Calendar dates from 01/01/2019 to 12/31/2025.

 col_name | col_type
 year     | smallint
 month    | smallint
 date     | date

Sample results

 country | subscription_count
 AU      |                278
 CA      |                283

Solution postgres

WITH month_start_dt AS (
    SELECT date, EXTRACT('MONTH' FROM date) AS month
    FROM dates
    WHERE date >= '2023-01-01'
    AND date < '2023-05-01'
    AND EXTRACT('DAY' FROM date) = 1
    COUNT(DISTINCT subscription_id) AS subscription_count
FROM apple_subscription S
JOIN month_start_dt D
ON S.start_at <= D.date
AND S.end_at >= D.date
WHERE S.state = 'paid'
GROUP BY country;


This query is used to count the number of unique, paid subscriptions by country for the first four months of the year 2023.

Here's the breakdown:

  • The first part of the query (the WITH clause) creates a temporary table called month_start_dt that contains the dates and their corresponding months. It only includes the dates that are between January 1, 2023 and April 30, 2023, and only the first day of each of those months.

  • The main part of the query then selects from the apple_subscription table and joins it with the month_start_dt table. The join condition is that the subscription's start date (start_at) is before or on the same day as the date from the month_start_dt table, and the subscription's end date (end_at) is after or on the same day as the date from the month_start_dt table. This means that the subscription was active on the first day of the month.

  • The query then filters to only include subscriptions that are paid (state = 'paid').

  • Finally, the query groups the results by country and counts the number of unique subscription IDs for each country (COUNT(DISTINCT subscription_id)), which gives the number of unique, paid subscriptions by country for each of the first four months of 2023.

Please note that this query assumes that the start_at and end_at dates are inclusive, meaning that a subscription is considered active on both its start and end dates. If this is not the case, the join conditions may need to be adjusted.

Expected results

More Apple questions

ID Title Level FTPR
203 Top product by country by month apple hard
204 AB testing sanity check apple easy
205 Search results recall apple medium
206 Search results recall by testing group apple medium
207 Results shown rate by group apple medium
208 Top 3 urls by testing groups apple hard