UptoSkills Logo
Login
Median Customer Order Value by RegionHard

Median Customer Order Value by Region

HardSQLPractice library · +30 XPpercentiles and medianssqlwindow functions

A retail company wants to understand the typical spending of its customers in different regions to tailor marketing strategies. They have data on customer orders and their assigned sales regions.

Tables:

  • customers (customer_id, region)
  • orders (order_id, customer_id, order_total)

Return the region and the median_order_total. The results should be ordered alphabetically by region.

Handle NULL order_total by excluding them from median calculation. If a region has no orders, its median should be NULL.

Tables

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  region varchar(50) NOT NULL
);
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(customer_id),
  order_total numeric(10, 2)
);

Example data

INSERT INTO customers (customer_id, region) VALUES
(1, 'North'),
(2, 'South'),
(3, 'East'),
(4, 'West'),
(5, 'North'),
(6, 'South'),
(7, 'East'),
(8, 'West'),
(9, 'North'),
(10, 'Central');

INSERT INTO orders (order_id, customer_id, order_total) VALUES
(101, 1, 150.00),
(102, 2, 200.00),
(103, 3, 120.00),
(104, 4, 300.00),
(105, 1, 180.00),
(106, 5, 220.00),
(107, 6, 250.00),
(108, 7, 110.00),
(109, 8, 320.00),
(110, 2, 190.00),
(111, 3, 130.00),
(112, 9, 160.00),
(113, 10, 500.00),
(114, 6, 260.00),
(115, 8, 310.00),
(116, 5, 210.00);

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

Hints

Similar problems

More in: SQL and Databases problems