- **********************************************************************************************************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
)
SELECT
country,
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;
Explanation
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_dtthat 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_subscriptiontable and joins it with themonth_start_dttable. The join condition is that the subscription's start date (start_at) is before or on the same day as the date from themonth_start_dttable, and the subscription's end date (end_at) is after or on the same day as the date from themonth_start_dttable. 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