WITH very_simple_cte AS (
    SELECT 0
)
SELECT * FROM very_simple_cte;

-- EN: simple CTE example returning 2 lines
-- FR: CTE simple retournant 2 lignes
WITH simple_cte(x) AS ( 
    SELECT 0 
    UNION ALL 
    SELECT 0
)
SELECT * FROM simple_cte;

WITH simple_cte_bis(x) AS ( 
    SELECT 0
)
SELECT * FROM simple_cte_bis
UNION ALL
SELECT * FROM simple_cte_bis
;


-- EN: Simple recursive CTE example
-- FR: Exemple d’un CTE récursif basique
WITH RECURSIVE recursive_cte(x) AS ( 
    SELECT  0 
    UNION ALL 
    SELECT  x
    FROM    recursive_cte
    LIMIT   10
)
SELECT * FROM recursive_cte;



-- EN: simple generator example, create numbers form 0 to 15
-- FR: générateur siple, créant des nombres de 0 à 15
WITH RECURSIVE generator(x) AS ( 
    SELECT 0 
        UNION ALL 
    SELECT  x + 1 
    FROM    generator 
    WHERE   x < 15 
)
SELECT * FROM generator;



-- EN: number generator by step of 5
-- FR: générateur de nombre par pas de 5
WITH RECURSIVE generator_step(x) AS ( 
    SELECT 0 
        UNION ALL 
    SELECT  x + 5 
    FROM    generator_step 
    WHERE   x < 100
)
SELECT * FROM generator_step;



-- EN: double each number
-- FR: double chaque nombre
WITH RECURSIVE generator_double(x) AS ( 
    SELECT 1 
        UNION ALL 
    SELECT  x * 2 
    FROM    generator_double 
    LIMIT   30 
)
SELECT * FROM generator_double;


WITH RECURSIVE generator_fibo (p, n) AS
(
     SELECT 0,
            1

     UNION ALL
     
     SELECT n,
            p + n
     FROM   generator_fibo
     WHERE  n < 100
)
SELECT  p
FROM    generator_fibo;



-- EN: This generates a list of month for one 1995
-- FR: Ceci génère une liste des mois de 1995
WITH RECURSIVE by_month(d) AS ( 
    SELECT '1995-01-01' 
        UNION ALL 
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month 
    WHERE   d < '1995-12-01' 
) 
SELECT * FROM by_month;

-- EN: This generates a list of month for current year
-- FR: Ceci génère une liste des mois de l’année courante
WITH RECURSIVE by_month(d) AS ( 
    SELECT strftime('%Y', 'now')||'-01-01' 
    
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month 
    WHERE   d < (strftime('%Y', 'now')||'-12-01') 
) 
SELECT * FROM by_month;


-- EN: Better, by respecting DRY
-- FR: Mieux, en respectant le DRY
WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
),
by_month(d) AS ( 
    SELECT  const.y||'-01-01'
    FROM    const
        
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month, const
    WHERE   d < (const.y||'-12-01') 
) 
SELECT * FROM by_month;




-- EN: Same, with some limits
-- FR: Même cas, mais avec des limites
WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
),by_month(d) AS ( 
    SELECT  const.y||'-01-01'
    FROM    const
    
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS')
    FROM    by_month, const
    WHERE   d < (const.y||'-12-01') 
)
SELECT  d AS date_start,
        date(
            d,
            'start of month',
            '+1 month',
            '-1 day'
        ) AS date_end
FROM    by_month;


-- EN: Based on previous example, this creates trimesters for 2021
-- FR: Basé sur l’exemple précédent, ceci crée les trimestres de 2021 
WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
), by_trimester(d) AS ( 
    SELECT  const.y||'-01-01' 
    FROM    const
    
    UNION ALL 
    
    SELECT  DATE(d, '+3 MONTHS') 
    FROM    by_trimester 
    LIMIT   4 
) 
SELECT  d AS date_start,
        date(
            d,
            'start of month',
            '+3 month',
            '-1 day'
        ) AS date_end
FROM    by_trimester;



-- EN: Now, let’s create alphabet!
-- FR: Et maintenant, créons l’alphabet !
WITH RECURSIVE alphabet(code) AS ( 
    SELECT 65 
    
    UNION ALL 
    
    SELECT  code + 1 
    FROM    alphabet 
    WHERE   code < (65 + 25) 
) 
SELECT CHAR(code) FROM alphabet;


-- EN: Example table
-- FR: La table nous servant d’exemple
CREATE TABLE comments (
    id          INTEGER PRIMARY KEY UNIQUE,
    parent_id   INTEGER REFERENCES comments(id),
    author      TEXT NOT NULL
                CHECK(author <> '' AND LENGTH(TRIM(author)) > 0),
    content     TEXT NOT NULL
                DEFAULT '',
    created_at  DATETIME NOT NULL
                DEFAULT (datetime('now'))
);


INSERT INTO comments (author, content, created_at) VALUES('caesar', 'Alea iacta est!', '2021-01-11 08:00:00');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(1, 'brutus', 'blablabla', '2021-01-11 09:10:03');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(2, 'caesar', 'Tu quoque fili?', '2021-01-11 09:33:45');
INSERT INTO comments (author, content, created_at) VALUES('quidam', 'Quo vadis?', '2021-01-11 09:34:23');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(4, 'caesar', 'Ego vadam Romam', '2021-01-11 09:45:10');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(5, 'spqr', 'Quid??!!!', '2021-01-11 09:46:00');
INSERT INTO comments (author, content, created_at) VALUES('lambda', 'LOL PTDR XOXO', '2021-01-11 14:03:56');


INSERT INTO comments (author, content, created_at) VALUES('quidam', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', '2021-01-13 15:04:57');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(4, 'quidam', 'Pellentesque finibus elit id scelerisque molestie.', '2021-01-14 21:39:08');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(5, 'quidam', 'Phasellus suscipit, ligula eget vehicula tempus, dui turpis egestas ante, eu pretium diam ligula eget urna.', '2021-01-29 20:32:00');
INSERT INTO comments (author, content, created_at) VALUES('quidam', 'Nam sit amet nisi quis quam efficitur vehicula.', '2021-02-05 23:42:00');
INSERT INTO comments (author, content, created_at) VALUES('caesar', 'Aenean iaculis arcu neque. Integer tincidunt turpis sed ipsum egestas, quis fermentum mi ornare!', '2021-02-15 02:54:00');
INSERT INTO comments (author, content, created_at) VALUES('brutus', 'Sed aliquet est tortor, sit amet scelerisque sem varius et. Nulla dolor purus, fermentum et urna ut, tempus facilisis ante?!', '2021-02-20 03:25:00');
INSERT INTO comments (author, content, created_at) VALUES('brutus', 'Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus :)', '2021-02-05 23:42:00');
INSERT INTO comments (author, content, created_at) VALUES('lambda', 'Sed arcu sem, facilisis ut malesuada interdum, fringilla sed lorem.', '2021-03-18 18:04:20');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(12, 'spqr', 'Donec neque metus, pretium ut pellentesque eu, tempus at nisl.', '2021-03-25 15:04:12');
INSERT INTO comments (author, content, created_at) VALUES('lambda', 'Proin molestie nulla at quam semper euismod.', '2021-03-26 23:55:23');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(12, 'spqr', 'Sed porta et magna non tempor. Duis ornare lorem non ipsum consectetur suscipit.', '2021-05-02 16:10:00');
INSERT INTO comments (author, content, created_at) VALUES('visitor', 'Etiam sagittis ligula vitae nulla laoreet, et maximus erat convallis.', '2021-05-07 12:47:00');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(15, 'spqr', 'Ut scelerisque ac est vulputate mollis.', '2021-06-06 07:56:00');
INSERT INTO comments (author, content, created_at) VALUES('lambda', 'Cras mattis magna in tellus aliquam, condimentum convallis eros blandit.', '2021-06-10 22:22:22');
INSERT INTO comments (author, content, created_at) VALUES('caesar', 'Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae ;-)', '2021-07-31 23:59:00');
INSERT INTO comments (author, content, created_at) VALUES('brutus', 'Interdum et malesuada fames ac ante ipsum primis in faucibus. Aliquam imperdiet lorem nunc, ac interdum nisl euismod at.', '2021-08-15 12:00:00');
INSERT INTO comments (author, content, created_at) VALUES('lol', 'Fusce quis tellus volutpat, tempus ligula sed, consectetur ipsum. LOL !!!! :-D', '2021-08-30 13:40:30');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(21, 'spqr', 'Nulla euismod nulla urna, in viverra neque viverra eu.', '2021-09-01 09:10:11');
INSERT INTO comments (author, content, created_at) VALUES('caligula', 'In ornare sollicitudin quam, sit amet blandit metus porta ac.', '2021-10-01 17:40:50');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(24, 'spqr', 'Nulla sed porttitor libero, ac finibus nisl. Suspendisse potenti.', '2021-10-24 12:51:00');
INSERT INTO comments (author, content, created_at) VALUES('rosa', 'Morbi elementum, quam ut aliquet ornare, leo urna bibendum urna, a ultricies turpis sem ut sapien.', '2021-10-25 00:41:00');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(6, 'spqr', 'Ut a sem ornare tellus faucibus tincidunt a vel est.', '2021-11-09 12:34:00');
INSERT INTO comments (author, content, created_at) VALUES('brutus', 'Nam eu velit vel ex tristique iaculis at id nisl. Nunc vitae rhoncus erat.', '2021-11-11 23:21:00');
INSERT INTO comments (author, content, created_at) VALUES('lambda', 'In fermentum arcu sem, quis viverra nulla sodales sit amet.', '2021-12-08 09:42:00');
INSERT INTO comments (author, content, created_at) VALUES('caesar', 'Phasellus in nulla nec mauris scelerisque pellentesque. Donec ac quam dignissim, ornare nisi ac, pharetra urna.', '2021-12-09 19:26:07');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(16, 'spqr', 'Nullam imperdiet faucibus scelerisque.', '2021-12-25 04:24:34');
INSERT INTO comments (author, content, created_at) VALUES('quidam', 'Pellentesque lobortis faucibus purus, nec varius massa luctus ut.', '2021-12-28 12:32:00');
INSERT INTO comments (parent_id, author, content, created_at) VALUES(18, 'spqr', 'Nam vel ligula mi.', '2021-12-31 23:58:00');










-- EN: Number of comments by trimester
-- FR: Nombre de commentaires par trimestre
WITH RECURSIVE by_trimester(d) AS ( 
    SELECT '2021-01-01' 
    
    UNION ALL 
    
    SELECT  DATE(d, '+3 MONTHS') 
    FROM    by_trimester 
    LIMIT   4 
),
comments_by_date AS (
    SELECT      created_at
    FROM        comments 
),
borned_trimester AS (
    SELECT  d AS date_start,
            date(
                d,
                'start of month',
                '+3 month',
                '-1 day'
            ) AS date_end
    FROM    by_trimester
)
SELECT  t.date_start,
        t.date_end,
        COUNT(1) AS amount
FROM    comments_by_date AS c,
        borned_trimester AS t
WHERE   strftime('%Y-%m-%d', c.created_at) >= t.date_start
        AND
        strftime('%Y-%m-%d', c.created_at) <= t.date_end
GROUP BY t.date_start
        ;




-- EN: Number of comments, by first letters of their author
-- FR: Nombre de commentaires classés par ordre alphabétique de leurs auteurs pour chaque lettre de l’alphabet
WITH RECURSIVE alphabet(code) AS ( 
    SELECT 65 
        UNION ALL 
    SELECT  code + 1 
    FROM    alphabet 
    WHERE   code < (65 + 25) 
),
by_authors (letter, amount) AS (
    SELECT  UPPER(SUBSTR(author, 1,1)) AS letter,
            COUNT(*) AS amount
    FROM    comments
    GROUP BY UPPER(SUBSTR(author, 1,1))
)
SELECT      CHAR(code) AS letter,
            COALESCE(amount, 0) AS comments
FROM        alphabet AS a
LEFT JOIN   by_authors AS b ON b.letter = CHAR(a.code);




-- EN: Tree of each entry
-- FR: Arborescence pour chaque entrée
CREATE VIEW comments_tree AS
    WITH RECURSIVE parents AS (
        SELECT  id                          AS id,
                0                           AS ancestors,
                UPPER(author)||':'||CHAR(10)||substr(content, 1,50)||'…'  AS tree
        FROM    comments
        WHERE   parent_id IS NULL

        UNION
        
        SELECT  child.id            AS id,
                c.ancestors + 1     AS ancestors,
                (
                    char(10) || c.tree 
                    || char(10) 
                    || printf("%"||(2 * (c.ancestors + 1)) || "s", ' ') 
                    || UPPER(child.author) || ':'
                    || char(10) 
                    || printf("%"||(2 * (c.ancestors + 1)) || "s", ' ') 
                    || substr(child.content, 1, (50 - (2 * (c.ancestors + 1))))
                    ||'…'
                ) AS tree
        FROM comments as child
        INNER JOIN parents c ON c.id = child.parent_id
    )
    SELECT id, tree FROM parents;

