Skip to content

Instantly share code, notes, and snippets.

@antoine-lizee

antoine-lizee/medium_cte_and_optim_3.sql

Last active October 21, 2020 01:49
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/bdff5285b345bc04ad6d6ed131cb20be to your computer and use it in GitHub Desktop.
Save antoine-lizee/bdff5285b345bc04ad6d6ed131cb20be to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment