A/B-  SQL. 18   
 


    A/B-  SQL    .  18    : ,  ,  ,    ,  ,     ,   .     SQLite.





 

A/B-  SQL. 18   





  





 SQL   A/B-


SQL    ,       .

     , ,    ,    .

      : , , ,   .




 


      .

        , -      .

    ,         .




  


1.     (#task-1)

2.    (#task-2)

3.      (#task-3)

4.      (#litres_trial_promo)

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.    



 

   A  B   ,       .




CREATE TABLE experiment(user_id INTEGER, variant TEXT, paid INTEGER);

INSERT INTO experiment VALUES (1,'A',1),(2,'A',0),(3,'A',0),(4,'B',1),(5,'B',1),(6,'B',0);




SELECT variant,

COUNT(*) AS users,

SUM(paid) AS payers,

ROUND(1.0 * SUM(paid) / COUNT(*), 2) AS conversion

FROM experiment

GROUP BY variant

ORDER BY variant;


 

[["A", 3, 1, 0.33], ["B", 3, 2, 0.67]]


 

SUM(paid)  , COUNT(*)   ,     .

     ,           .

ORDER BY      :        .


  

   ,         .


 

    ?  ,     .

:  B:          A.




 2.   



 

 ,      ,        .




CREATE TABLE assignments(user_id INTEGER, variant TEXT);

INSERT INTO assignments VALUES (1,'A'),(2,'A'),(3,'A'),(4,'B'),(5,'B');




SELECT variant, COUNT(*) AS users

FROM assignments

GROUP BY variant

ORDER BY variant;


 

[["A", 3], ["B", 2]]


 

GROUP BY variant     .

     ,           .

ORDER BY      :        .


  

       ,      .


 

    ?

: B   , A  .




 3.     



 

   ,       A  B.




CREATE TABLE assignments(user_id INTEGER, variant TEXT);

INSERT INTO assignments VALUES (1,'A'),(2,'A'),(2,'B'),(3,'B');




SELECT user_id, COUNT(DISTINCT variant) AS variants_seen

FROM assignments

GROUP BY user_id

HAVING COUNT(DISTINCT variant) > 1

ORDER BY user_id;


 

[[2, 2]]


 

COUNT(DISTINCT variant) ,     .

     ,           .

ORDER BY      :        .


  

   ,        .


 

   ?  ,     .

:  2   .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/a-b-metriki-v-sql-18-zadach-bez-magii-74419849/)  .

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


