Count, Return, Find, and Select in SQL Queries

orders

+----------+--------------+------------+--------+
| order_id | customer_id  | order_date | amount |
+----------+--------------+------------+--------+
| 1        | 101          | 2024-01-05 | 250.00 |
| 2        | 102          | 2024-01-07 | 125.50 |
| 3        | 101          | 2024-01-10 | 80.00  |
| 4        | 103          | 2024-01-11 | 300.00 |
| 5        | 104          | 2024-01-15 | 150.00 |
+----------+--------------+------------+--------+

Scenario

SQL screening – answer four basic queries on a single orders table

Question

Count how many orders each customer has made. Return the total revenue generated per day. Find the customer(s) with the highest single order amount. Select all orders whose amount is above the overall average.

Hints

GROUP BY, ORDER BY, HAVING and window functions might help.

Orders per customer count

For each customer, count how many orders they have placed.

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
250	101	2024-01-05	1
125.5	102	2024-01-07	2
80	101	2024-01-10	3
300	103	2024-01-11	4
150	104	2024-01-15	5

Expected output

Each customer with the number of orders they placed.

customer_id	order_count
101	2
102	1
103	1
104	1

Hints

1. Group rows by customer_id.
2. Use COUNT(*) to count orders.

Daily total revenue

Calculate the total revenue generated for each order_date.

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
250	101	2024-01-05	1
125.5	102	2024-01-07	2
80	101	2024-01-10	3
300	103	2024-01-11	4
150	104	2024-01-15	5

Expected output

Total revenue per day, one row per order_date.

order_date	total_revenue
2024-01-05	250
2024-01-07	125.5
2024-01-10	80
2024-01-11	300
2024-01-15	150

Hints

1. Aggregate with SUM(amount).
2. Group by order_date.

Highest single order amount

Find the order(s) with the highest single order amount and return order_id, customer_id, and amount.

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
250	101	2024-01-05	1
125.5	102	2024-01-07	2
80	101	2024-01-10	3
300	103	2024-01-11	4
150	104	2024-01-15	5

Expected output

The order(s) tied for the maximum single order amount.

amount	customer_id	order_id
300	103	4

Hints

1. Find MAX(amount) first.
2. Filter orders where amount equals that maximum.

Orders above average amount

Select all orders whose amount is greater than the overall average order amount.

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
250	101	2024-01-05	1
125.5	102	2024-01-07	2
80	101	2024-01-10	3
300	103	2024-01-11	4
150	104	2024-01-15	5

Expected output

All orders with amount greater than the overall average amount.

amount	customer_id	order_date	order_id
250	101	2024-01-05	1
300	103	2024-01-11	4

Hints

1. Compute the overall AVG(amount) in a subquery.
2. Filter orders with amount greater than that average.