  SQL. 18   
 


     SQL  ,    SELECT, JOIN  GROUP BY     ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD,  ,  , ,   .  18     :   ,   ,   , ,  ,    .     ,       SQLite.





 

  SQL. 18   





  





    


      ,        .    GROUP BY,         .      :           ,       .

         . GROUP BY   . SUM(amount) OVER (...)         .       :   ,     .

   SQLite.   ,         .  PostgreSQL, MySQL 8       ,       .




  


     :    .     ,   ,  ,    .      SQL,     -.

       ,   ORDER BY.     ORDER BY  OVER,        .    ORDER BY   .

        .     ;      ,        .




  


1. ROW_NUMBER:     (#task-1)

2. RANK  DENSE_RANK:     (#task-2)

3.     (#task-3)

4.      (#litres_trial_promo)

5. LAG:     (#litres_trial_promo)

6. LEAD:      (#litres_trial_promo)

7. FIRST_VALUE  LAST_VALUE:     (#litres_trial_promo)

8.      (#litres_trial_promo)

9. COUNT  AVG    (#litres_trial_promo)

10. NTILE:      (#litres_trial_promo)

11. PERCENT_RANK  CUME_DIST:    (#litres_trial_promo)

12. :    (#litres_trial_promo)

13. Gaps and islands:     (#litres_trial_promo)

14. :       (#litres_trial_promo)

15. -2     (#litres_trial_promo)

16. :     (#litres_trial_promo)

17.       (#litres_trial_promo)

18.  :       (#litres_trial_promo)




 





 1. ROW_NUMBER:    



 

       ,   id   ,     .




CREATE TABLE orders(client TEXT, order_id TEXT, order_date TEXT, amount INTEGER);

INSERT INTO orders VALUES

('alice','A1','2026-09-01',1200),

('alice','A2','2026-09-05',700),

('bob','B1','2026-09-02',400),

('bob','B2','2026-09-02',900),

('bob','B3','2026-09-06',300);




WITH numbered AS (

SELECT

client,

order_id,

order_date,

amount,

ROW_NUMBER() OVER (

PARTITION BY client

ORDER BY order_date DESC, order_id DESC

) AS rn

FROM orders

)

SELECT client, order_id, amount

FROM numbered

WHERE rn = 1

ORDER BY client;


 

[

[

"alice",

"A2",

700

],

[

"bob",

"B3",

300

]

]


  

PARTITION BY client      .   alice  bob    .

ORDER BY   ,    .      ,  order_id     .

 MAX(order_date)      id    . ROW_NUMBER    .


 

     ,       .    :   B1,  B2.


 

  alice  A3     2026-09-05   900.       ?

   (#navigation)




 2. RANK  DENSE_RANK:    



 

                  .




CREATE TABLE sku_sales(category TEXT, sku TEXT, revenue INTEGER);

INSERT INTO sku_sales VALUES

('books','A',1000),

('books','B',1000),

('books','C',800),

('templates','D',700),

('templates','E',600),

('templates','F',600);




SELECT

category,

sku,

revenue,

RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,

DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk

FROM sku_sales

ORDER BY category, rnk, sku;


 

[

[

"books",

"A",

1000,

1,

1

],

[

"books",

"B",

1000,

1,

1

],

[

"books",

"C",

800,

3,

2

],

[

"templates",

"D",

700,

1,

1

],

[

"templates",

"E",

600,

2,

2

],

[

"templates",

"F",

600,

2,

2

]

]


  

RANK    :      ,   .

DENSE_RANK   :     .     -2    .

       ,    PARTITION BY category.


 

  ,  RANK  DENSE_RANK.     ,        .


 

 C  1000.        books?

   (#navigation)




 3.    



 

               .




CREATE TABLE daily_sales(day TEXT, revenue INTEGER);

INSERT INTO daily_sales VALUES

('2026-09-01',1200),

('2026-09-02',500),

('2026-09-03',900),

('2026-09-04',400);




SELECT




  .


   .

   ,     (https://www.litres.ru/book/raznoe/okonnye-funktsii-sql-18-zadach-dlia-analitika-74418588/)  .

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


