Identify Top Discounts and Monitor Code Submissions

Products

+------------+----------+-------+----------+
| product_id | category | price | discount |
+------------+----------+-------+----------+
| 101        | phone    | 699   | 50       |
| 102        | phone    | 799   | 60       |
| 201        | laptop   | 999   | 150      |
| 202        | laptop   | 1099  | 150      |
| 301        | watch    | 199   | 20       |
+------------+----------+-------+----------+

Submissions

+---------------+-------------+-----------+
| submission_id | employer_id | status    |
+---------------+-------------+-----------+
| 1             | 555         | buggy     |
| 2             | 555         | non-buggy |
| 3             | 777         | buggy     |
| 4             | 777         | buggy     |
| 5             | 777         | non-buggy |
+---------------+-------------+-----------+

Scenario

E-commerce analytics and code-evaluation dashboard: identify top-discounted products per category and monitor buggy vs non-buggy code submissions for each employer.

Question

For every product category, return the product that has the maximum discount; if several products share that discount, choose the one with the smallest product_id. For each employer_id, count how many submissions are buggy and how many are non-buggy.

Hints

Use GROUP BY with MAX discount and MIN product_id tie-breaker; use conditional aggregation COUNT(CASE WHEN status='buggy' THEN 1 END).

Top Discount Per Category

For each product category, return the single product that has the maximum discount; if multiple products share the maximum discount, choose the one with the smallest product_id. Output category, product_id, price, and discount.

Table Products

column	type	constraints
product_id	INTEGER	PRIMARY KEY, NOT NULL
category	VARCHAR(20)	NOT NULL
price	DECIMAL(10,2)	NOT NULL
discount	INTEGER	NOT NULL

Sample data:

category	discount	price	product_id
phone	50	699	101
phone	60	799	102
laptop	150	999	201
laptop	150	1099	202
watch	20	199	301

Table Submissions

column	type	constraints
submission_id	INTEGER	PRIMARY KEY, NOT NULL
employer_id	INTEGER	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

employer_id	status	submission_id
555	buggy	1
555	non-buggy	2
777	buggy	3
777	buggy	4
777	non-buggy	5

Expected output

One row per category containing the product with the highest discount; ties are broken by the smallest product_id.

category	discount	price	product_id
laptop	150	999	201
phone	60	799	102
watch	20	199	301

Hints

1. Use a window function (ROW_NUMBER) to rank products within each category.
2. Order by discount descending and product_id ascending to implement the tie-breaker.

Buggy vs Non-buggy Counts

For each employer_id, count how many submissions are buggy and how many are non-buggy. Return employer_id, buggy_count, and non_buggy_count.

Table Products

column	type	constraints
product_id	INTEGER	PRIMARY KEY, NOT NULL
category	VARCHAR(20)	NOT NULL
price	DECIMAL(10,2)	NOT NULL
discount	INTEGER	NOT NULL

Sample data:

category	discount	price	product_id
phone	50	699	101
phone	60	799	102
laptop	150	999	201
laptop	150	1099	202
watch	20	199	301

Table Submissions

column	type	constraints
submission_id	INTEGER	PRIMARY KEY, NOT NULL
employer_id	INTEGER	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

employer_id	status	submission_id
555	buggy	1
555	non-buggy	2
777	buggy	3
777	buggy	4
777	non-buggy	5

Expected output

One row per employer_id with counts of buggy and non-buggy submissions.

buggy_count	employer_id	non_buggy_count
1	555	1
2	777	1

Hints

1. Use conditional aggregation with CASE expressions or filtered COUNTs.
2. Group by employer_id and aggregate buggy vs non-buggy in separate columns.