00.output

    n = 1
 name = f

    n = 2
 name = fo

    n = 3
 name = foo

    n = 4
 name = fooo

    n = 5
 name = foooo

00rec.sql

WITH RECURSIVE f(n, name) AS (
  SELECT 1 as n, 'f' as name
  UNION ALL SELECT n+1 as n, 'f'|| substr('oooooooooooo', 1, n) as name FROM f LIMIT 5
) SELECT * from f

01.output

   id = 1
 name = x

   id = 2
 name = y

   id = 3
 name = z

01team.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
)
SELECT
  id,
  name
FROM
 teams

02.output

  team_id = 1
team_name = x
  user_id = 10
user_name = foo

  team_id = 1
team_name = x
  user_id = 20
user_name = bar

  team_id = 3
team_name = z
  user_id = 30
user_name = boo

02inner-join.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
),
users as (
  SELECT 10 as id, 'foo' as name, 1 as team_id
  UNION ALL SELECT 20, 'bar', 1
  UNION ALL SELECT 30, 'boo', 3
)
SELECT
  t.id as team_id,
  t.name as team_name,
  u.id as user_id,
  u.name as user_name
FROM
  teams as t
  INNER JOIN users as u ON t.id = u.team_id

03.output

  team_id = 1
team_name = x
 count(*) = 2

  team_id = 3
team_name = z
 count(*) = 1

03count.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
),
users as (
  SELECT 10 as id, 'foo' as name, 1 as team_id
  UNION ALL SELECT 20, 'bar', 1
  UNION ALL SELECT 30, 'boo', 3
)
SELECT
  t.id as team_id,
  t.name as team_name,
  count(*)
FROM
  teams as t
  INNER JOIN users as u ON t.id = u.team_id
GROUP BY
  t.id

04.output

  team_id = 1
team_name = x
        c = 2

  team_id = 2
team_name = y
        c = 0

  team_id = 3
team_name = z
        c = 1

04outer-join.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
),
users as (
  SELECT 10 as id, 'foo' as name, 1 as team_id
  UNION ALL SELECT 20, 'bar', 1
  UNION ALL SELECT 30, 'boo', 3
)
SELECT
  t.id as team_id,
  t.name as team_name,
  sum(CASE WHEN u.id is NULL then 0 ELSE 1 END) as c
FROM
  teams as t
  LEFT OUTER JOIN users as u ON t.id = u.team_id
GROUP BY
  t.id

05.output

  team_id = 1
team_name = x
  user_id = 10
user_name = foo

  team_id = 1
team_name = x
  user_id = 20
user_name = bar

  team_id = 1
team_name = x
  user_id = 30
user_name = boo

  team_id = 2
team_name = y
  user_id = 10
user_name = foo

  team_id = 3
team_name = z
  user_id = 20
user_name = bar

05many-to-many.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
),
users as (
  SELECT 10 as id, 'foo' as name -- x,yに所属
  UNION ALL SELECT 20, 'bar' -- x,zに所属
  UNION ALL SELECT 30, 'boo' -- xに所属
),
teams2users as (
  SELECT 1 as team_id, 10 as user_id
  UNION ALL SELECT 1, 20
  UNION ALL SELECT 1, 30
  UNION ALL SELECT 2, 10
  UNION ALL SELECT 3, 20
)
SELECT
  t.id as team_id,
  t.name as team_name,
  u.id as user_id,
  u.name as user_name
FROM
  teams as t
  INNER JOIN users as u
  INNER JOIN teams2users as xref ON t.id = xref.team_id AND xref.user_id = u.id

06.output

1           foo       
1           bar       
1           boo       
2           foo       
2           bar       
2           boo       
3           foo       
3           bar       
3           boo       

06cross-join-simple.sql

WITH xs AS (
  SELECT 1 as id UNION ALL SELECT 2 UNION ALL SELECT 3
),
ys AS (
  SELECT 'foo' as name UNION ALL SELECT 'bar' UNION ALL SELECT 'boo'
)
SELECT * FROM xs CROSS JOIN ys

07.output

     year = 2019
team_name = x
        c = 1
    names = foo

     year = 2020
team_name = x
        c = 3
    names = foo,bar,boo

     year = 2020
team_name = y
        c = 1
    names = foo

     year = 2021
team_name = x
        c = 1
    names = boo

     year = 2021
team_name = y
        c = 1
    names = foo

     year = 2021
team_name = z
        c = 1
    names = bar

     year = 2022
team_name = x
        c = 1
    names = boo

     year = 2022
team_name = y
        c = 1
    names = foo

07cross-join.sql

WITH teams AS (
  SELECT 1 as id, 'x' as name
  UNION ALL SELECT 2, 'y'
  UNION ALL SELECT 3, 'z'
),
users as (
  SELECT 10 as id, 'foo' as name -- x,yに所属
  UNION ALL SELECT 20, 'bar' -- x,zに所属
  UNION ALL SELECT 30, 'boo' -- xに所属
),
teams2users as (
  SELECT 1 as team_id, 10 as user_id, '2019' as start, '2020' as `end`
  UNION ALL SELECT 1, 20, '2020', '2020'
  UNION ALL SELECT 1, 30, '2020', NULL
  UNION ALL SELECT 2, 10, '2020', NULL
  UNION ALL SELECT 3, 20,  '2021', '2021'
),
years as (
  SELECT '2019' as year
  UNION ALL SELECT '2020'
  UNION ALL SELECT '2021'
  UNION ALL SELECT '2022'
)
SELECT
  y.year as year,
  t.name as team_name,
  sum(CASE WHEN u.id is NULL then 0 ELSE 1 END) as c,
  group_concat(u.name) as names
FROM
  teams as t
  INNER JOIN teams2users as xref ON t.id = xref.team_id
  LEFT OUTER JOIN users as u ON xref.user_id = u.id
  CROSS JOIN years as y ON xref.start <= y.year AND (xref.`end` IS NULL OR xref.`end` >= y.year)
GROUP BY
  y.year, t.id, t.name

Makefile

default: 00 01 02 03 04 05 06 07

00:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
01:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
02:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
03:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
04:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
05:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output
06:
	cat $(shell echo $@*.sql) | sqlite3 -column | tee $@.output
07:
	cat $(shell echo $@*.sql) | sqlite3 -line | tee $@.output