CTE  SQL. 18   WITH  
 


   CTE  WITH  SQL     .  18    :  ,  ,   ,  ,   ,  , -join    .     SQLite.





 

CTE  SQL. 18   WITH  





  





    WITH


CTE             .

      :    ,   ,     .

   WITH   ,     .    ,      .




  


   CTE   :           .

   SELECT.     :  ,          .

    SQLite.  WITH      PostgreSQL, MySQL   SQL-.




  


1.       (#task-1)

2.     (#task-2)

3.     (#task-3)

4.       (#litres_trial_promo)

5.   CTE  (#litres_trial_promo)

6.     (#litres_trial_promo)

7.       (#litres_trial_promo)

8.      CTE (#litres_trial_promo)

9.     (#litres_trial_promo)

10.     CTE (#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 orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);




WITH paid_orders AS (

SELECT id, total

FROM orders

WHERE status = 'paid'

)

SELECT SUM(total) AS paid_revenue

FROM paid_orders;


 

[[800]]


 

CTE paid_orders    .

       .

  ,  new  cancelled    .


  

      ,       .


 

 paid-  200.   paid_revenue  ?

: paid_revenue  1000,       paid_orders.




 2.    



 

      ,      .




CREATE TABLE orders(id INTEGER, client_id INTEGER, total INTEGER);

INSERT INTO orders VALUES (1,1,300),(2,1,500),(3,2,900),(4,3,100);




WITH client_revenue AS (

SELECT client_id, SUM(total) AS revenue

FROM orders

GROUP BY client_id

)

SELECT client_id, revenue

FROM client_revenue

ORDER BY revenue DESC, client_id;


 

[[2, 900], [1, 800], [3, 100]]


 

CTE     client_id.

 SELECT      .

       .


  

    ,  ,  client_revenue      .


 

  3   850.    ?

:  3     950,   2   1.




 3.    



 

   ,     500,     .




CREATE TABLE sales(sku TEXT, revenue INTEGER);

INSERT INTO sales VALUES ('A1',200),('A1',350),('B2',100),('C3',700);




WITH sku_revenue AS (

SELECT sku, SUM(revenue) AS revenue

FROM sales

GROUP BY sku

)

SELECT sku, revenue

FROM sku_revenue

WHERE revenue >= 500

ORDER BY revenue DESC;


 

[["C3", 700], ["A1", 550]]


 

 CTE    SKU.

 WHERE    .

B2  ,      100.


  

WHERE revenue >= 500       ,    .


 

 A1  ,      A1   500?

:   CTE   200  350,     550.




  .


   .

   ,     (https://www.litres.ru/book/raznoe/cte-v-sql-18-zadach-s-with-dlia-analitika-74419472/)  .

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


