UptoSkills Logo
Login
Sales Performance: Rank by Consecutive UpswingHard

Sales Performance: Rank by Consecutive Upswing

HardSQLPractice library · +30 XPwindow functionssqlranking

A retail company wants to analyze the sales performance of its products. They are particularly interested in identifying products that show sustained growth over a period.

Tables:

  • products: product_id (INTEGER, PRIMARY KEY), product_name (VARCHAR(50))
  • sales: sale_id (INTEGER, PRIMARY KEY), product_id (INTEGER), sale_date (DATE), quantity (INTEGER), amount (NUMERIC(10,2))

Return the product_name and its upswing_rank. The upswing_rank should be based on how many consecutive days the product's total sales amount increased compared to the previous day. A product starting its first recorded sale has an upswing count of 0. If a product has no sales, it should not be included. If two products have the same number of consecutive upswing days, the product with the higher product_id should rank lower (meaning a smaller rank number).

Output columns: product_name, upswing_rank Order the results by upswing_rank ascending.

Tables

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(product_id),
  sale_date DATE NOT NULL,
  quantity INTEGER NOT NULL,
  amount NUMERIC(10,2) NOT NULL
);

Example data

INSERT INTO products (product_id, product_name) VALUES
(1, 'Laptop'),
(2, 'Keyboard'),
(3, 'Mouse'),
(4, 'Monitor'),
(5, 'Webcam');

INSERT INTO sales (sale_id, product_id, sale_date, quantity, amount) VALUES
(101, 1, '2023-01-01', 2, 2000.00),
(102, 1, '2023-01-02', 1, 1000.00), -- Down
(103, 1, '2023-01-03', 3, 3000.00), -- Up
(104, 1, '2023-01-04', 2, 2500.00), -- Down
(105, 1, '2023-01-05', 1, 1200.00), -- Down
(106, 2, '2023-01-01', 10, 100.00),
(107, 2, '2023-01-02', 15, 150.00), -- Up
(108, 2, '2023-01-03', 12, 120.00), -- Down
(109, 2, '2023-01-04', 8, 80.00),   -- Down
(110, 3, '2023-01-01', 5, 50.00),  -- First day for Mouse, count 0
(111, 3, '2023-01-02', 6, 60.00),  -- Up
(112, 3, '2023-01-03', 7, 70.00),  -- Up
(113, 3, '2023-01-04', 5, 55.00); -- Down

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

Hints

Similar problems

More in: SQL and Databases problems