CTE in practise: Writing a building block SQL expression with Postgresql

0-click-trial_shop.sql

WITH 
shops_on_trial AS (
    SELECT shopify_domain 
    FROM shops 
    INNER JOIN charges on shops.id = charges.shop_id
    WHERE charges.processor = 'trial' AND (commenced_at <= now()) 
    AND (cancelled_at IS NULL OR cancelled_at >= now())
),

stats AS (
    SELECT
    shop_statistics.shop_domain,
    SUM(cross_sell_clicks) as clicks_count
    FROM shops_on_trial
    INNER JOIN shop_statistics ON shop_statistics.shop_domain = shops_on_trial.shopify_domain
    WHERE shop_statistics.date >= '2021-07-12T00:00:00+00:00' AND shop_statistics.date <= '2021-08-10T23:59:59+00:00'
    GROUP BY shop_statistics.shop_domain
    HAVING SUM(cross_sell_clicks) = 0
),

shops_on_trial_0_click AS (
  SELECT 
    shops.*, 
    clicks_count AS "Total clicks"

  FROM shops
  INNER JOIN stats ON shops.shopify_domain = stats.shop_domain
)

-- SELECT * FROM stats;
SELECT * FROM shops_on_trial_0_click;

active-paid-customer-number-of-conversion.sql

WITH 
paid_shops AS (
    SELECT DISTINCT ON(shopify_domain) shopify_domain,shop_plan, plans.name as plan_name, currency 
    FROM shops 
    INNER JOIN charges on shops.id = charges.shop_id
    INNER JOIN plans ON charges.plan_id = plans.id
    WHERE charges.processor = 'shopify' AND (commenced_at <= now()) 
          AND (cancelled_at IS NULL OR cancelled_at >= now())
    -- order by shopify_domain, charges.amount DESC
),


shop_aggregators AS (
    SELECT 
		paid_shops.shopify_domain,
		DATE_TRUNC('month', shop_statistics.date) as "Month", 
        
		SUM(cross_sell_clicks) as "Total Clicks", 
		SUM(cross_sell_order_count) AS "Conversions Count",
        COALESCE( SUM(cross_sell_order_value::float), 0 ) as "Conversion Total"
		
    FROM paid_shops
		
    INNER JOIN shop_statistics ON shop_statistics.shop_domain = paid_shops.shopify_domain
    -- Current month 
    -- WHERE shop_statistics.date >= date_trunc('month', CURRENT_DATE)
    WHERE shop_statistics.date >= {start_time} AND shop_statistics.date < {end_time}
    GROUP BY shopify_domain, DATE_TRUNC('month', shop_statistics.date)
    ORDER by "Month" DESC
),

reports AS ( 
  SELECT paid_shops.shopify_domain AS "Shop URL", 
         paid_shops.shop_plan AS "Shop Plan",
         paid_shops.plan_name AS "Plan Name",
         "Total Clicks",
         "Conversions Count", 
         round("Conversion Total"::numeric, 2) AS "Conversion Total", 
         paid_shops.currency, 
         shop_aggregators."Month"
  FROM paid_shops INNER JOIN shop_aggregators
  ON shop_aggregators.shopify_domain = paid_shops.shopify_domain
)

-- SELECT * FROM active_paid_charges;
-- SELECT * FROM paid_shops;
-- SELECT * FROM shop_aggregators;
SELECT * FROM reports ORDER BY "Conversions Count" DESC;

cero-click-remaining.sql

WITH


  shop_with_plan AS (
    SELECT 
		 DISTINCT ON(shops.shopify_domain) shopify_domain AS shopify_domain,
		 plans.name AS plan_name,
		 COALESCE( plans.clicks, 0 )  AS plan_clicks,
		 commenced_at,
		 cancelled_at,
		 charges.id AS charge_id,
		 charges.status,
		 charges.amount,
		 
		 EXTRACT(DAY FROM commenced_at),
		 EXTRACT(DAY FROM CURRENT_DATE),
		 
         DATE_TRUNC('month', CURRENT_DATE - interval '1' month),
         DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + ( EXTRACT(DAY FROM commenced_at) * interval '1 day'),
         
        CASE WHEN EXTRACT(DAY FROM commenced_at) < EXTRACT(DAY FROM CURRENT_DATE)  THEN  DATE_TRUNC('month', CURRENT_DATE) +  ( ( EXTRACT(DAY FROM commenced_at) -1 )* interval '1 day')
            ELSE  DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + (( EXTRACT(DAY FROM commenced_at) - 1) * interval '1 day')
       END AS "commence_recur_at"
	FROM shops 
    INNER JOIN charges on charges.shop_id = shops.id
    INNER JOIN plans on charges.plan_id   = plans.id

    WHERE 
    --   charges.processor = 'shopify' AND 
      (commenced_at <= now()) 
      AND (cancelled_at IS NULL OR cancelled_at >= now())
      AND plans.clicks IS NOT NULL
    ORDER BY shops.shopify_domain, charges.amount DESC
  ),
	
  shop_with_plan_sanity AS (
		SELECT  DISTINCT ON (shopify_domain) * 
		FROM  shop_with_plan
		ORDER BY shopify_domain, amount DESC
  ),
  
  shop_with_total_clicks AS
  (
    SELECT  
	  shop_statistics.shop_domain,
	  COALESCE( SUM(shop_statistics.clicks),  0 ) AS total_click
	  
	  
    FROM shop_statistics
    INNER JOIN shop_with_plan ON shop_statistics.shop_domain = shop_with_plan.shopify_domain

    WHERE  shop_statistics.date >= shop_with_plan.commence_recur_at
      
    GROUP BY shop_statistics.shop_domain
  ),
	
  shop_with_click_remaining AS (
	SELECT 
		shopify_domain, 
		plan_name,
		plan_clicks,
		total_click,
	    (plan_clicks - total_click) AS remaining_click,
		commenced_at,
		commence_recur_at,
        cancelled_at,
        charge_id

	FROM shop_with_plan 
	INNER JOIN shop_with_total_clicks ON shop_with_total_clicks.shop_domain = shop_with_plan.shopify_domain
	)

-- SELECT * FROM shop_with_plan WHERE shopify_domain = 'coral-reef-swim.myshopify.com';
-- SELECT * FROM shop_with_plan_sanity WHERE shopify_domain = 'coral-reef-swim.myshopify.com';
-- SELECT * FROM shop_with_plan;

SELECT * FROM shop_with_click_remaining
WHERE remaining_click <= 0
ORDER BY remaining_click ASC;

plan click with remaining.sql

WITH 
shops_with_plan AS (
    SELECT
      DISTINCT ON(shops.shopify_domain) shopify_domain, 
      shops.id,
      plans.name AS plan_name,
      plans.clicks AS plan_clicks,
      charges.amount as charge_amount,
      commenced_at,
      
      EXTRACT(DAY FROM commenced_at),
      EXTRACT(DAY FROM CURRENT_DATE),
      
      DATE_TRUNC('month', CURRENT_DATE - interval '1' month),
      DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + ( EXTRACT(DAY FROM commenced_at) * interval '1 day'),
             
      CASE
        WHEN EXTRACT(DAY FROM commenced_at) < EXTRACT(DAY FROM CURRENT_DATE) 
          THEN DATE_TRUNC('month', CURRENT_DATE) + ( ( EXTRACT(DAY FROM commenced_at) -1 )* interval '1 day')
        ELSE
          DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + (( EXTRACT(DAY FROM commenced_at) - 1) * interval '1 day')
       END AS commence_recur_at

    FROM shops 
    INNER JOIN charges ON charges.shop_id = shops.id
    INNER JOIN plans ON charges.plan_id = plans.id
    WHERE 
      commenced_at <= now() AND ( cancelled_at IS NULL OR cancelled_at >= now() ) 
    ORDER BY shops.shopify_domain, charges.id DESC
),

shop_with_total_clicks AS (
  SELECT
    shop_domain, 
    COALESCE( SUM(shop_statistics.clicks),  0 ) AS total_clicks
  FROM shop_statistics
  INNER JOIN shops_with_plan ON shops_with_plan.shopify_domain = shop_statistics.shop_domain
  WHERE shop_statistics.date >= commence_recur_at
  GROUP BY shop_domain
),

shop_plan_total_clicks AS (
  SELECT 
    shops.id AS id,
    shops.shopify_domain,
    plan_name,
    charge_amount,
    plan_clicks,
    total_clicks,

    CASE
        WHEN plan_clicks IS NULL
          THEN NULL
        ELSE
         plan_clicks - total_clicks
       END AS remaining

  FROM shops
  INNER JOIN shops_with_plan AS shops_with_plan ON shops.shopify_domain = shops_with_plan.shopify_domain
  LEFT JOIN shop_with_total_clicks AS shop_with_total_clicks ON shop_with_total_clicks.shop_domain = shops.shopify_domain
)

-- 
-- SELECT id AS "ID",
--       shopify_domain AS "Shop",
--       plan_name AS "Plan",
--       plan_clicks AS "Plan Clicks",
--       commenced_at AS "Start time"
-- FROM shops_with_plan order by shopify_domain ASC;
--
-- SELECT * FROM shop_with_total_clicks;
SELECT     
    id AS id,
    shopify_domain AS "Shopify Domain" ,
    plan_name AS "Plan Name",
    charge_amount AS "Charge Amount",
    plan_clicks AS "Plan Clicks",
    total_clicks AS "Total Clicks",
    remaining as "Remaining"
FROM shop_plan_total_clicks
ORDER BY total_clicks DESC;

CTE is a great way to write your SQL to avoid nested unreadable subqueries. It does not use much in rails because Rails has a great DSL but you would find CTE very useful when you have to write a prototype solution in SQL to solve a problem or when you have to write a raw SQL to generate a report in BI tool like Blazer.