SQLPad
Sign In

203. Top product by country by month

hard apple
  • ********************************202.
  • ******************************************************************************************
  • ***************************************************************************
  • *******************************************************
  • *********************************************************************************************************************

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 | product_name | subscription_count | month | ranking
---------+--------------+--------------------+-------+---------
 AU      | iCloud 50GB  |                  1 |     1 |       1
 AU      | Netflix      |                  1 |     1 |       1
 AU      | Apple Music  |                  1 |     1 |       1
 AU      | Spotify      |                 23 |     2 |       1
 AU      | Apple Music  |                 28 |     3 |       1
 AU      | Apple Music  |                 36 |     4 |       1
 CA      | ChatGPT Plus |                  2 |     1 |       1
 CA      | iCloud 50GB  |                  2 |     1 |       1

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
),
procut_subscriptions AS (
        SELECT
        country,
        product_name,
        month,
        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, product_name, month
),
product_ranking AS (
    SELECT country, product_name, month, subscription_count,  
    RANK() OVER (PARTITION BY country, month ORDER BY subscription_count DESC) AS ranking
    FROM procut_subscriptions
)
SELECT country, product_name, month, subscription_count, ranking
FROM product_ranking
WHERE ranking = 1;

Explanation

This query is used to find the most popular paid product subscription in each country for each month from January 2023 to April 2023.

Here is a breakdown of the query:

  • The month_start_dt common table expression (CTE) selects the first day of each month for the given date range ('2023-01-01' to '2023-05-01') from the dates table.
  • The procut_subscriptions CTE counts the number of distinct subscriptions for each product in each country for each month. It only counts a subscription if the subscription start date is on or before the first day of the month and the end date is after the first day of the month, and the subscription state is 'paid'.
  • The product_ranking CTE assigns a rank to each product in each country for each month based on the subscription count, with the product having the highest subscription count getting the rank 1.
  • Finally, the main query selects the products that have a rank of 1 in their respective country and month, which are the most popular products for each month in each country.

So, this query essentially tells you which paid product had the most active subscriptions on the first day of each month from January 2023 to April 2023 in each country.

Expected results


More Apple questions

ID Title Level FTPR
202 Monthly active paid subscriptions apple hard
50%
204 AB testing sanity check apple easy
50%
205 Search results recall apple medium
30%
206 Search results recall by testing group apple medium
30%
207 Results shown rate by group apple medium
50%
208 Top 3 urls by testing groups apple hard
10%