UptoSkills Logo
Login
Rank Sales by Consecutive DaysMedium

Rank Sales by Consecutive Days

MediumSQLPractice library · +20 XPwindow functionssqlranking

UptoSkills Arena is tracking sales performance. You need to rank each salesperson based on their consecutive days where their individual sales exceeded the overall company average daily sales for that month. This helps identify consistent performers.

Tables:

  • salespeople: salesperson_id (INT, PK), name (VARCHAR(100))
  • sales: sale_id (INT, PK), salesperson_id (INT, FK), sale_date (DATE), amount (NUMERIC(10,2))

Return the salesperson_id, name, the sale_date, and the calculated rank. The rank should be determined by the number of consecutive days a salesperson had sales above the monthly average. If there are ties in consecutive days, the salesperson with the earlier sale_date for the start of that consecutive streak gets a lower rank. Handle NULLs by ignoring salespersons with no sales. Empty results are possible if no one meets the criteria. The final output should be ordered by salesperson_id and then sale_date.

Tables

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  salesperson_id INTEGER NOT NULL,
  sale_date DATE NOT NULL,
  amount NUMERIC(10,2) NOT NULL,
  FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id)
);

Example data

INSERT INTO salespeople (salesperson_id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO sales (sale_id, salesperson_id, sale_date, amount) VALUES 
(1, 1, DATE '2023-01-05', 150.00), 
(2, 1, DATE '2023-01-06', 200.00), 
(3, 2, DATE '2023-01-05', 100.00), 
(4, 2, DATE '2023-01-06', 120.00), 
(5, 1, DATE '2023-02-10', 300.00), 
(6, 1, DATE '2023-02-11', 350.00), 
(7, 1, DATE '2023-02-12', 400.00), 
(8, 3, DATE '2023-02-10', 50.00);
-- Jan avg: (150+200+100+120)/4 = 130. Alice (150,200) > 130, Bob (100,120) < 130.
-- Feb avg: (300+350+400+50)/4 = 225. Alice (300,350,400) > 225, Charlie (50) < 225.
-- Alice Jan: 2 consecutive days > 130.
-- Alice Feb: 3 consecutive days > 225. Rank 1.
-- Bob Jan: 0 consecutive days > 130.
-- Charlie Feb: 0 consecutive days > 225.
-- Overall, Alice has a streak of 3 days. Rank 1. Bob and Charlie have 0 streaks. Tie for last.

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems