Skip to content

Instantly share code, notes, and snippets.

@esquinas

esquinas/sql_fiddling.sql

Last active September 2, 2022 15:35
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 esquinas/cf9eff17497fc0486b81fe75a58d89e2 to your computer and use it in GitHub Desktop.
Save esquinas/cf9eff17497fc0486b81fe75a58d89e2 to your computer and use it in GitHub Desktop.
Download ZIP
How to play with SQL with no actual table (POSTGRES flavor)
Raw
sql_fiddling.sql
-- WINDOW EXAMPLE:
SELECT id
, col1
, col2
, bool_col
, datecuen
, (CASE WHEN bool_col IS NOT NULL
THEN SUM(col1) OVER win1
+ SUM(col2) OVER win1
ELSE 0
END) AS sum_col12_over_id
FROM (-- id col1 col2 bool_col datecuen
VALUES (0, 42, 2, TRUE, '2021-01-01')
, (1, 0, 10, TRUE, '2021-02-22')
, (1, 1, 10, TRUE, '2021-03-23')
, (1, 2, 10, TRUE, '2021-04-14')
, (1, 3, 10, TRUE, '2021-05-05')
, (2, 1, 0, FALSE, '2021-06-26')
, (3, 33, 99, FALSE, '2021-07-17')
, (3, 33, 1, NULL, '2022-01-02')
) AS table1(id, col1, col2, bool_col, datecuen)
WINDOW win1 AS (PARTITION BY id)
;
-- JOIN EXAMPLE:
WITH table1(id, col1, datecuen) AS (
VALUES (0, 0, '2020-01-01')
, (1, 11, '2021-01-01')
, (2, 42, '2021-02-22')
, (3, 33, '2021-06-26')
, (4, 34, '2021-07-17')
, (5, 35, '2022-01-02')
, (6, 636, '2022-01-02')
)
, table2(id, col2, datecuen) AS (
VALUES (1, 1, '2021-01-01')
, (2, 2, '2021-02-22')
, (3, 3, '2021-06-26')
, (4, 4, '2021-07-17')
, (5, 5, '2022-01-02')
, (6, 6, '2022-01-02')
, (7, 7, '2022-01-03')
)
SELECT table1.id
, table1.col1
, table2.col2
FROM table1
JOIN table2
ON table1.id = table2.id
;
-- BALANCE EXAMPLE: LINK <https://winand.at/sql-slides-for-developers>
WITH transactions(id, account, amount) AS (
VALUES ( 1, '104001', +10)
, ( 2, '502001', +20)
, ( 3, '502001', -10)
, ( 4, '703001', +50)
, ( 5, '703001', -30)
, ( 6, '703001', -20)
, ( 7, '104001', +1)
)
SELECT id
, amount
, SUM(amount)
OVER ( PARTITION BY account
ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS balance
, account
FROM transactions
ORDER BY id
;
-- BANK EXAMPLE:
WITH people(id, datecuen, balance) AS (
VALUES (1, '2022-01-11', 42)
, (2, '2022-01-10', 0)
, (3, '2022-02-22', 84)
, (4, '2022-03-31', 0)
)
SELECT *
, balance - COALESCE( LAG(balance)
OVER(ORDER BY datecuen)
, 0) AS delta
FROM people
;
-- PIVOT TABLE EXAMPLE:
WITH invoices(id, amount, datecuen ) AS (
VALUES ( 1, 0.10, '2020-01-01'::date )
, ( 2, 180.30, '2020-01-07'::date )
, ( 3, 307.75, '2020-01-14'::date )
, ( 4, 792.65, '2020-02-02'::date )
, ( 5, 109.60, '2020-02-17'::date )
, ( 6, 891.70, '2020-02-02'::date )
, ( 7, 276.35, '2020-03-03'::date )
, ( 8, 267.90, '2020-03-06'::date )
, ( 9, 244.85, '2020-03-06'::date )
, (10, 221.60, '2020-04-04'::date )
, (11, 587.35, '2020-05-05'::date )
, (12, 696.60, '2020-06-06'::date )
, (13, 861.85, '2020-07-07'::date )
, (14, 271.10, '2020-07-17'::date )
, (15, 171.85, '2020-08-08'::date )
, (16, 476.70, '2020-08-18'::date )
, (17, 460.90, '2020-09-09'::date )
, (18, 571.20, '2020-09-19'::date )
, (19, 974.65, '2020-10-10'::date )
, (20, 147.50, '2020-10-20'::date )
, (21, 805.75, '2020-11-11'::date )
, (22, 910.30, '2020-11-28'::date )
, (23, 337.99, '2020-12-12'::date )
, (24, 422.95, '2020-12-19'::date )
, (25, 9.75, '2020-12-28'::date )
-- 2021
, (26, 464.50, '2021-01-01'::date )
, (27, 580.50, '2021-01-07'::date )
, (28, 711.00, '2021-01-14'::date )
, (29, 295.00, '2021-02-02'::date )
, (30, 416.50, '2021-02-17'::date )
, (31, 520.00, '2021-02-02'::date )
, (32, 297.50, '2021-03-03'::date )
, (33, 650.00, '2021-03-06'::date )
, (34, 970.50, '2021-04-04'::date )
, (35, 123.00, '2021-04-14'::date )
, (36, 379.00, '2021-05-05'::date )
, (37, 459.50, '2021-05-15'::date )
, (38, 89.00, '2021-06-06'::date )
, (39, 71.00, '2021-07-07'::date )
, (40, 312.00, '2021-07-30'::date )
, (41, 795.00, '2021-08-08'::date )
, (42, 685.50, '2021-09-09'::date )
, (43, 718.50, '2021-09-19'::date )
, (44, 258.50, '2021-10-10'::date )
, (45, 82.00, '2021-10-20'::date )
, (46, 686.50, '2021-11-11'::date )
, (47, 89.50, '2021-11-28'::date )
, (48, 752.00, '2021-12-12'::date )
, (49, 125.00, '2021-12-19'::date )
, (50, 86.50, '2021-12-27'::date )
)
SELECT year
, SUM(amount) AS yearly_revenue
, SUM(amount) FILTER (WHERE month = 1) AS jan
, SUM(amount) FILTER (WHERE month = 2) AS feb
, SUM(amount) FILTER (WHERE month = 3) AS mar
, SUM(amount) FILTER (WHERE month = 4) AS abr
, SUM(amount) FILTER (WHERE month = 5) AS may
, SUM(amount) FILTER (WHERE month = 6) AS jun
, SUM(amount) FILTER (WHERE month = 7) AS jul
, SUM(amount) FILTER (WHERE month = 8) AS ago
, SUM(amount) FILTER (WHERE month = 9) AS sep
, SUM(amount) FILTER (WHERE month = 10) AS oct
, SUM(amount) FILTER (WHERE month = 11) AS nov
, SUM(amount) FILTER (WHERE month = 12) AS dec
, COUNT(id) AS invoice_count
FROM (SELECT invoices.*
, EXTRACT(YEAR FROM datecuen) AS year
, EXTRACT(MONTH FROM datecuen) AS month
FROM invoices
) invoices
GROUP BY year
ORDER BY year
;
-- LATERAL TABLE EXAMPLE:
WITH
products AS (
SELECT n AS id
, round((random() * 3000)::decimal, 2) AS price
, 'Product #' || n AS name
FROM generate_series(1, 1000) AS n
),
wishlists(id, full_name, desired_price) AS (
VALUES (1, 'Alice Alpha', 450)
, (2, 'Bob Beta', 60)
, (3, 'Charlie C.', 1500)
, (4, 'Daniel Delta', 200)
)
SELECT wishlists.full_name
, wishlists.desired_price
, top_products.price AS product_price
, top_products.id AS product_id
, top_products.name AS product_name
FROM wishlists
, LATERAL
(
SELECT *
FROM products
WHERE products.price <= wishlists.desired_price
ORDER BY products.price DESC
LIMIT 3
) AS top_products -- Top products under the desired price on their Wishlist
ORDER BY wishlists.id,
price DESC
@esquinas
Copy link
Author

esquinas commented May 25, 2022 •
edited

Result for window example:

id col1 col2 bool_col datecuen sum_col12_over_id
0 42 2 TRUE 2021-01-01 44
1 0 10 TRUE 2021-02-22 46
1 1 10 TRUE 2021-03-23 46
1 2 10 TRUE 2021-04-14 46
1 3 10 TRUE 2021-05-05 46
2 1 0 FALSE 2021-06-26 1
3 33 99 FALSE 2021-07-17 166
3 33 1 null 2022-01-02 0

@esquinas
Copy link
Author

Result for join example:

id col1 col2
1 11 1
2 42 2
3 33 3
4 34 4
5 35 5
6 636 6

@esquinas
Copy link
Author

Result for the balance example:

id amount balance account
1 10 10 104001
2 20 20 502001
3 -10 10 502001
4 50 50 703001
5 -30 20 703001
6 -20 0 703001
7 1 11 104001

@esquinas
Copy link
Author

Result for the bank example:

id datecuen balance delta
2 2022-01-10 0 0
1 2022-01-11 42 42
3 2022-02-22 84 42
4 2022-03-31 0 -84

@esquinas
Copy link
Author

Result for the pivot table example:

year yearly_revenue jan feb mar abr may jun jul ago sep oct nov dec invoice_count
2020 10999.24 488.15 1793.95 789.10 221.60 587.35 696.60 1132.95 648.55 1032.10 1122.15 1716.05 770.69 25
2021 10618.00 1756.00 1231.50 947.50 1093.50 838.50 89.00 383.00 795.00 1404.00 340.50 776.00 963.50 25

@esquinas
Copy link
Author

esquinas commented Sep 2, 2022

Result for the lateral table example:

full_name desired_price product_price product_id product_name
Alice Alpha 450 446.08 824 Product #824
Alice Alpha 450 444.29 491 Product #491
Alice Alpha 450 437.22 197 Product #197
Bob Beta 60 56.59 57 Product #57
Bob Beta 60 52.66 738 Product #738
Bob Beta 60 52.64 151 Product #151
Charlie C. 1500 1498.78 459 Product #459
Charlie C. 1500 1496.07 238 Product #238
Charlie C. 1500 1493.47 41 Product #41
Daniel Delta 200 194.39 781 Product #781
Daniel Delta 200 191.69 957 Product #957
Daniel Delta 200 188.94 265 Product #265

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment