antoine-lizee · October 21, 2020 01:49
diff --git a/medium_cte_and_optim_3.sql b/medium_cte_and_optim_3.sql
 -- Show the distribution of orders from senior customers per month:
 WITH senior_customers AS (  -- [This would not be a pb if PG 12+]
 	SELECT *
 	FROM remote.customers
 	WHERE age > 60
 )
 SELECT date_trunc(paid_date) AS order_month, count(*) AS n_orders
 FROM remote.orders o  -- brings back to the local server ALL the orders
 JOIN senior_customers c ON c.id = o.customer_id  -- do a JOIN without the indices from the remote
	-- Show the distribution of orders from senior customers per month:
	WITH senior_customers AS ( -- [This would not be a pb if PG 12+]
	SELECT *
	FROM remote.customers
	WHERE age > 60
	)
	SELECT date_trunc(paid_date) AS order_month, count(*) AS n_orders
	FROM remote.orders o -- brings back to the local server ALL the orders
	JOIN senior_customers c ON c.id = o.customer_id -- do a JOIN without the indices from the remote