UptoSkills Logo
Login
Median Order Value Per Customer GroupHard

Median Order Value Per Customer Group

HardSQLPractice library · +30 XPpercentiles and medianssqlwindow functions

A food delivery platform wants to understand the spending behavior of its customers. They've categorized customers into groups based on their total spending. For each customer group, determine the median order value.

Tables:

  • customers: customer_id (int, PK), name (varchar(100))
  • orders: order_id (int, PK), customer_id (int, FK), order_value (numeric(10,2)), order_date (date)

Return the customer group name (derived from total spending), and the median order value for that group. The groups are defined as follows:

  • 'Low Spender': Total spending < 500
  • 'Mid Spender': Total spending between 500 and 1500 (inclusive)
  • 'High Spender': Total spending > 1500

Order the results by the derived group name alphabetically. Handle ties in order value by considering the lower order value as appearing first. Handle NULL order values by ignoring them. If a group has no orders, it should not appear in the result.

Tables

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  name varchar(100)
);
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  order_value numeric(10,2),
  order_date date NOT NULL
);

Example data

INSERT INTO customers (customer_id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve');
INSERT INTO orders (order_id, customer_id, order_value, order_date) VALUES
(101, 1, 150.50, '2023-01-10'),
(102, 1, 200.00, '2023-02-15'),
(103, 2, 600.75, '2023-01-20'),
(104, 2, 750.25, '2023-03-01'),
(105, 2, 900.00, '2023-04-05'),
(106, 3, 1600.00, '2023-02-01'),
(107, 3, 1700.50, '2023-03-10'),
(108, 4, 50.00, '2023-01-15'),
(109, 4, 100.00, '2023-02-20'),
(110, 5, 1200.00, '2023-03-05');

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

Hints

Similar problems

More in: SQL and Databases problems