  SQL. 18     
 


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





 

  SQL. 18     





  





   


   ,    -  , ,    .

  ,           .        .

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

9.    (#litres_trial_promo)

10.     (#litres_trial_promo)

11.     CTR (#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.      



 

  ,        SKU,       .




CREATE TABLE offer_stats(sku TEXT, views INTEGER, carts INTEGER, paid_orders INTEGER);

INSERT INTO offer_stats VALUES

('A',100,5,2),

('B',60,1,0),

('C',40,0,0);




SELECT sku,

views,

carts,

paid_orders,

ROUND(carts * 100.0 / views, 2) AS cart_conversion_pct,

CASE WHEN carts = 0 THEN 0

ELSE ROUND(paid_orders * 100.0 / carts, 2)

END AS order_from_cart_pct

FROM offer_stats

ORDER BY views DESC;


 

[["A", 100, 5, 2, 5.0, 40.0], ["B", 60, 1, 0, 1.67, 0.0], ["C", 40, 0, 0, 0.0, 0]]


 

cart_conversion_pct    .

order_from_cart_pct ,     .

     CASE,     .


  

   , SKU C   ,       .


 

 SKU    ,    ?

: SKU C:  ,   ,     .




 2.      



 

       ,   ,     .




CREATE TABLE offers(sku TEXT, status TEXT, stock INTEGER, freeze INTEGER, quarantine INTEGER);

INSERT INTO offers VALUES

('A','PUBLISHED',25,0,0),

('B','PUBLISHED',0,0,0),

('C','PUBLISHED',10,1,0),

('D','PUBLISHED',8,0,1),

('E','DRAFT',30,0,0);




SELECT sku, stock

FROM offers

WHERE status = 'PUBLISHED'

AND stock > 0

AND freeze = 0

AND quarantine = 0

ORDER BY sku;


 

[["A", 25]]


 

A     .

B ,    .

C  D  - freeze  quarantine.


  

  PUBLISHED    :       .


 

 DRAFT   30    ?

:      ,    DRAFT   .




 3.     



 

    , ,        .




CREATE TABLE orders(id INTEGER, status TEXT, gross INTEGER, market_fee INTEGER, acquiring INTEGER, tax INTEGER, cost INTEGER);

INSERT INTO orders VALUES

(1,'paid',1000,140,20,60,200),

(2,'paid',500,70,10,30,100),

(3,'new',900,126,18,54,180);




SELECT id,

gross,

gross - market_fee - acquiring - tax - cost AS net_profit

FROM orders

WHERE status = 'paid'

ORDER BY id;


 

[[1, 1000, 580], [2, 500, 290]]


 

    paid-.

 1  1000 - 140 - 20 - 60 - 200.

  3     .


  




  .


   .

   ,     (https://www.litres.ru/book/raznoe/marketpleis-v-sql-18-zadach-dlia-prodavtsa-i-analitika-74419422/)  .

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


