How to play with SQL with no actual table (POSTGRES flavor)

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

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

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