Skip to content

Instantly share code, notes, and snippets.

@antoine-lizee

antoine-lizee/medium_cte_and_optim_4.sql

Last active October 21, 2020 01:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save antoine-lizee/5a8164203824d1edc6d2105f2106e495 to your computer and use it in GitHub Desktop.
Save antoine-lizee/5a8164203824d1edc6d2105f2106e495 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
medium_cte_and_optim_4.sql
-- Get the number of orders for every customer signed in the last year:
WITH customers_with_orders AS MATERIALIZED ( -- [needs "MATERIALIZED" only for PG12+]
SELECT customer_id, signup_date, count(*) n_orders
FROM remote.customers c
JOIN remote.orders o ON c.id = o.customer_id
GROUP BY 1, 2
)
SELECT *
FROM customers_with_orders
WHERE signup_date > current_date - 365
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment