  SQL. 18   
 


      SQL.  18    :  ,  , , retention,  , revenue matrix,  ,  7,     .     SQLite.





 

  SQL. 18   





  





  


         ,   ,   .

        ,     .

   SQL-     BI-    .




 


      ,        .

  ,      :   .

    SQLite    .




  


1.      (#task-1)

2.    (#task-2)

3.      (#task-3)

4.    (#task-4)

5.      (#litres_trial_promo)

6.  retention rate (#litres_trial_promo)

7.      (#litres_trial_promo)

8.   revenue    (#litres_trial_promo)

9.     (#litres_trial_promo)

10.      (#litres_trial_promo)

11.     (#litres_trial_promo)

12.      (#litres_trial_promo)

13.  ,    (#litres_trial_promo)

14.      (#litres_trial_promo)

15.       (#litres_trial_promo)

16.      (#litres_trial_promo)

17.     (#litres_trial_promo)

18.    30  (#litres_trial_promo)




 





 1.     



 

       .    ,      .




CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');




SELECT client_id, substr(registered_at,1,7) AS cohort_month

FROM customers

ORDER BY client_id;


 

[[10, "2026-08"], [20, "2026-08"], [30, "2026-09"]]


 

substr  YYYY-MM    YYYY-MM-DD     .

     ,         SQLite.

 ORDER BY      ,        .


  

     ISO-,      .


 

    30?        .

: 2026-09.        .




 2.   



 

 ,       .    ,      .




CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');




SELECT substr(registered_at,1,7) AS cohort_month, COUNT(*) AS cohort_size

FROM customers

GROUP BY cohort_month

ORDER BY cohort_month;


 

[["2026-08", 2], ["2026-09", 1]]


 

GROUP BY           .

     ,         SQLite.

 ORDER BY      ,        .


  

    ;   retention     .


 

    ?

:  .        .




 3.     



 

     ,  ,  .




CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

CREATE TABLE orders(client_id INTEGER, paid_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');

INSERT INTO orders VALUES (10,'2026-08-16'),(10,'2026-09-02'),(30,'2026-09-03');




SELECT c.client_id, substr(c.registered_at,1,7) AS cohort_month, MIN(o.paid_at) AS first_paid_at

FROM customers c

LEFT JOIN orders o ON o.client_id = c.client_id

GROUP BY c.client_id, cohort_month

ORDER BY c.client_id;


 

[[10, "2026-08", "2026-08-16"], [20, "2026-08", null], [30, "2026-09", "2026-09-03"]]


 

LEFT JOIN    ,  MIN    ,   .

     ,         SQLite.

 ORDER BY      ,        .


  

INNER JOIN         .


 

    first_paid_at?        .

:   20.        .




 4.   



 

    ,       .




CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

CREATE TABLE orders(client_id INTEGER, paid_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');

INSERT INTO orders VALUES (10,'2026-08-16'),(30,'2026-09-03');







  .


   .

   ,     (https://www.litres.ru/book/raznoe/kogorty-v-sql-18-zadach-po-uderzhaniiu-74419932/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


