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 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