ArenaRunning Total of Delivery Charges per CityHard

Running Total of Delivery Charges per City

HardSQLPractice library · +30 XPrunning totalssqlwindow functions

UptoSkills Arena is a food delivery platform. We want to analyze the cumulative delivery charges incurred in each city over time to understand spending trends.

Tables:

cities: city_id (integer, PK), city_name (varchar(50))
deliveries: delivery_id (integer, PK), city_id (integer, FK), delivery_date (date), delivery_charge (numeric(10,2))

Return the city_name, delivery_date, delivery_charge, and the running total of delivery_charge for that city up to that date. The running total should be calculated for each city independently. For each city, the results should be ordered by delivery_date.

Handle NULL delivery_charge as 0. If a city has no deliveries, it should not appear in the result. If there are multiple deliveries on the same date for the same city, their charges should be summed before contributing to the running total.

Tables

CREATE TABLE cities (
    city_id integer PRIMARY KEY,
    city_name varchar(50) NOT NULL
);

CREATE TABLE deliveries (
    delivery_id integer PRIMARY KEY,
    city_id integer NOT NULL,
    delivery_date date NOT NULL,
    delivery_charge numeric(10,2)
);

Example data

INSERT INTO cities (city_id, city_name) VALUES
(1, 'Mumbai'),
(2, 'Delhi'),
(3, 'Bangalore');

INSERT INTO deliveries (delivery_id, city_id, delivery_date, delivery_charge) VALUES
(101, 1, '2023-01-10', 50.00),
(102, 2, '2023-01-10', 45.00),
(103, 1, '2023-01-11', 55.00),
(104, 3, '2023-01-11', 60.00),
(105, 2, '2023-01-12', 40.00),
(106, 1, '2023-01-12', 52.00),
(107, 1, '2023-01-10', 20.00), -- Duplicate date for Mumbai
(108, 2, '2023-01-11', NULL); -- NULL charge for Delhi

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

Running Total of Delivery Charges per City

Tables

Example data

Hints

Similar problems