  SQL. 18    
 


      SQL.  18    : , , ,  ,   , ,  ,   ,    .     SQLite.





 

  SQL. 18    





  





     


,      ,         .

   ,    : , , ,    .

    SQL-,             .




  


     ,    .

            .

  :     ,     .




  


1.      (#task-1)

2.     (#task-2)

3.       (#task-3)

4.       (#task-4)

5.      (#litres_trial_promo)

6.     (#litres_trial_promo)

7.      (#litres_trial_promo)

8.      (#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.      (#litres_trial_promo)




 





 1.     



 

 ,     ,   ,      .




CREATE TABLE events(user_id INTEGER, event_name TEXT);

INSERT INTO events VALUES

(1,'view'),(1,'cart'),(1,'pay'),(2,'view'),(2,'cart'),(3,'view'),(4,'view'),(4,'pay');




SELECT event_name, COUNT(DISTINCT user_id) AS users

FROM events

WHERE event_name IN ('view','cart','pay')

GROUP BY event_name

ORDER BY CASE event_name WHEN 'view' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END;


 

[["view", 4], ["cart", 2], ["pay", 2]]


 

COUNT(DISTINCT user_id)     ,    ,      .

     ,           .

ORDER BY      :        .


  

        ;      .


 

 pay   ?  ,     .

:    1  4.




 2.    



 

      :  ,  ,  .




CREATE TABLE events(user_id INTEGER, event_name TEXT);

INSERT INTO events VALUES

(1,'view'),(1,'cart'),(1,'pay'),(2,'view'),(2,'cart'),(3,'view');




SELECT user_id,

MAX(event_name = 'view') AS had_view,

MAX(event_name = 'cart') AS had_cart,

MAX(event_name = 'pay') AS had_pay

FROM events

GROUP BY user_id

ORDER BY user_id;


 

[[1, 1, 1, 1], [2, 1, 1, 0], [3, 1, 0, 0]]


 

   SQLite  0  1,  MAX       .

     ,           .

ORDER BY      :        .


  

      ,   1      .


 

 had_pay   2?  ,     .

: 0,     2   pay.




 3.      



 

         ,      .




CREATE TABLE events(user_id INTEGER, event_name TEXT);

INSERT INTO events VALUES

(1,'view'),(1,'cart'),(2,'view'),(2,'cart'),(3,'view'),(4,'view');




WITH flags AS (

SELECT user_id,

MAX(event_name = 'view') AS had_view,

MAX(event_name = 'cart') AS had_cart

FROM events

GROUP BY user_id

)

SELECT ROUND(1.0 * SUM(had_cart) / SUM(had_view), 2) AS view_to_cart

FROM flags;


 

[[0.5]]


 

       ,     .

     ,           .

ORDER BY      :        .


  

   ,        .


 

     ?  ,     .

: 0.5:        .




 4.      





  .


   .

   ,     (https://www.litres.ru/book/raznoe/voronki-v-sql-18-zadach-dlia-analitika-produkta-74419834/)  .

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


