LTV  SQL. 18     
 


    LTV  SQL    .  18    :  ,    , repeat rate, LTV  , , , CAC    .     SQLite.





 

LTV  SQL. 18     





  





 LTV      


LTV    ,    .

 ,   ,          .

       LTV-     .




   


     ,     ,   .

  :        .

        SQLite.




  


1.      (#task-1)

2.     (#task-2)

3.     (#task-3)

4.     (#task-4)

5.    (#litres_trial_promo)

6.     (#litres_trial_promo)

7.  LTV    (#litres_trial_promo)

8.     (#litres_trial_promo)

9.        (#litres_trial_promo)

10.  LTV     (#litres_trial_promo)

11.     LTV (#litres_trial_promo)

12.     (#litres_trial_promo)

13.     (#litres_trial_promo)

14.        (#litres_trial_promo)

15.  LTV  CAC (#litres_trial_promo)

16.  LTV   (#litres_trial_promo)

17.      (#litres_trial_promo)

18.   LTV- (#litres_trial_promo)




 





 1.     



 

    ,    LTV-   .




CREATE TABLE orders(client_id INTEGER, revenue INTEGER);

INSERT INTO orders VALUES (10,300),(10,500),(20,900),(30,200);




SELECT client_id, SUM(revenue) AS customer_revenue

FROM orders

GROUP BY client_id

ORDER BY customer_revenue DESC, client_id;


 

[[20, 900], [10, 800], [30, 200]]


 

SUM(revenue)  client_id     .

     ,         SQLite.

 ORDER BY      ,        .


  

   LTV    :      .


 

   ?        .

:  20   900.        .




 2.    



 

    ,      .




CREATE TABLE orders(client_id INTEGER, paid_at TEXT);

INSERT INTO orders VALUES (10,'2026-09-03'),(10,'2026-09-01'),(20,'2026-09-02');




SELECT client_id, MIN(paid_at) AS first_paid_at

FROM orders

GROUP BY client_id

ORDER BY client_id;


 

[[10, "2026-09-01"], [20, "2026-09-02"]]


 

MIN(paid_at)   YYYY-MM-DD     .

     ,         SQLite.

 ORDER BY      ,        .


  

      ,     .


 

     10?        .

: 2026-09-01.        .




 3.    



 

          .




CREATE TABLE orders(client_id INTEGER, order_id INTEGER);

INSERT INTO orders VALUES (10,1),(10,2),(20,3),(30,4),(30,5);




SELECT client_id, COUNT(*) AS orders_count

FROM orders

GROUP BY client_id

ORDER BY client_id;


 

[[10, 2], [20, 1], [30, 2]]


 

COUNT(*)       .

     ,         SQLite.

 ORDER BY      ,        .


  

DISTINCT client_id          .


 

   ?        .

:  10  30.        .




 4.    



 

       ,     .




CREATE TABLE orders(client_id INTEGER, revenue INTEGER);




  .


   .

   ,     (https://www.litres.ru/book/raznoe/ltv-v-sql-18-zadach-pro-klientov-i-pribyl-74419923/)  .

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


