medium_cte_and_optim_2.sql

-- Show the number of paid and delivered orders per country:
WITH orders_from_france AS (
	SELECT paid_date, delivered_date
	FROM orders 
	WHERE geo_country(lat, lon) = 'France'
)
, orders_by_paid_date AS (
	SELECT paid_date AS date_, count(*) AS n_paid_orders
	FROM orders_from_france
)
, orders_by_delivered_date AS (
	SELECT delivery_date AS date_, count(*) AS n_delivered_orders
	FROM orders_from_france
)
SELECT *
FROM orders_by_paid_date
JOIN orders_by_delivered_date USING (date_)