Last active
September 2, 2022 15:35
-
-
Save esquinas/cf9eff17497fc0486b81fe75a58d89e2 to your computer and use it in GitHub Desktop.
How to play with SQL with no actual table (POSTGRES flavor)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
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 |
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 |
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
Result for the balance example: