How to use WITH in a better way to parametrise a date

Question

I love to use CTEs to create nice and clear queries. However, I'm pretty sure the query I created is really inefficient.

Is there a better way to do this and keep things clear ?

with first_date as (
    -- selecting just 1 date 
    --    namely:  1 full year ago
    select (extract(year from current_date - interval '1 year')||'-01-01' )::date as date
    )
, last_date as (
    select date from star.dim_date where current_cal_day='Current'
)
, total_active_customers_ps_day as(
  select
        dd.date
        , dd.is_first_day_in_month
        , dd.is_last_day_in_month   
        , count(dc.id) as total_customers
    from first_date, last_date,
        star.dim_date dd
        -- join with dim_client, using first_subscription_start_date & last_subscription_end_date
        --   to get the ids of just the active clients 
        join star.dim_client dc on dd.date 
            between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01') 
            and dc.created <= dd.date
            and dc.first_subscription_start_date >= dc.created::date
    where 
        dd.date >= first_date.date
        and dd.date <= last_date.date
    group by 
        dd.date
        , dd.is_first_day_in_month 
        , dd.is_last_day_in_month   
)
select * from total_active_customers_ps_day ;

I think I'm causing some cartesian joins, since this query is far more efficient

with total_active_customers_ps_day as(
  select
        dd.date
        , dd.is_first_day_in_month
        , dd.is_last_day_in_month   
        , count(dc.id) as total_customers
    from 
        star.dim_date dd
        -- join with dim_client, using first_subscription_start_date & last_subscription_end_date
        --   to get the ids of just the active clients 
        join star.dim_client dc on dd.date 
            between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01') 
            and dc.created <= dd.date
            and dc.first_subscription_start_date >= dc.created::date
    where 
        dd.date >=  (extract(year from current_date - interval '1 year')||'-01-01' )::date
        and dd.date <= (select date from star.dim_date where current_cal_day='Current')
    group by 
        dd.date
        , dd.is_first_day_in_month 
        , dd.is_last_day_in_month   
)
select * from total_active_customers_ps_day ;
 

What's the better way to do this?

Answer 1

Instead of using a subquery in the WHERE clause to fetch the end date, you can calculate it once in a common table expression (CTE) at the beginning of the query, similar to what you did for the start date. This reduces the complexity of the WHERE clause and potentially makes the query more efficient.

There is indeed a Cartesian join between the star.dim_date table and the CTEs start_date and end_date. This could potentially lead to performance issues, especially if the star.dim_date table contains a large number of rows.

To avoid the Cartesian join and make the query more efficient, you can use a single CTE to calculate both the start and end dates and then join this CTE with the star.dim_date table.

Also I've used the DATE_TRUNC function to calculate the start date as one year ago from the current date, which can make the query more intuitive.

WITH date_range AS (
    SELECT
        (DATE_TRUNC('YEAR', CURRENT_DATE) - INTERVAL '1 YEAR')::DATE AS start_date,
        DATE_TRUNC('DAY', CURRENT_DATE) AS end_date
),

total_active_customers_per_day AS (
    SELECT
        dd.date,
        dd.is_first_day_in_month,
        dd.is_last_day_in_month,
        COUNT(dc.id) AS total_customers
    FROM
        star.dim_date dd
    JOIN
        star.dim_client dc ON dd.date BETWEEN dc.first_subscription_start_date AND COALESCE(dc.last_subscription_end_date::DATE, '3000-01-01')
            AND dc.created <= dd.date
            AND dc.first_subscription_start_date >= dc.created::DATE
    JOIN
        date_range ON dd.date BETWEEN date_range.start_date AND date_range.end_date
    GROUP BY
        dd.date,
        dd.is_first_day_in_month,
        dd.is_last_day_in_month
)

SELECT * FROM total_active_customers_per_day;

Ensure that the columns used in the JOIN conditions and the WHERE clause are properly indexed. In your case, indexing columns like dd.date, dc.first_subscription_start_date, and dc.last_subscription_end_date can significantly improve query performance.