Dream11: Gross Winnings, Tax Withheld and Net Payout per User
The payments team at Dream11 prepares a statement of every winner's payout for the week.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #625
Problem statement
The payments team at Dream11 prepares a statement of every winner's payout for the week. For this exercise use a simplified rule: tax is withheld at 30% of any single winning of more than 10,000 rupees, and a winning of 10,000 or less is paid in full.
For each user in Winning, return user_id, gross_winnings (the sum of their winnings), tds_deducted (the total tax withheld under the rule, applied winning by winning) and net_payout (gross minus tax). Order the rows by net_payout descending, then by user_id ascending.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: Winning
| Column | Type |
|---|---|
| winning_id | int |
| user_id | int |
| contest_id | int |
| amount | int |
Primary key: winning_id.
One row per prize a user won in a contest, in rupees. Amounts are multiples of 10, so 30% is always a whole number of rupees.
Examples
Example 1
Winning
| winning_id | user_id | contest_id | amount |
|---|---|---|---|
| 1 | 11 | 701 | 10000 |
| 2 | 11 | 702 | 15000 |
| 3 | 12 | 701 | 25000 |
| 4 | 13 | 703 | 500 |
| 5 | 13 | 704 | 10010 |
| 6 | 14 | 705 | 9000 |
| 7 | 12 | 706 | 2000 |
| 8 | 15 | 707 | 9000 |
Output
| user_id | gross_winnings | tds_deducted | net_payout |
|---|---|---|---|
| 11 | 25000 | 4500 | 20500 |
| 12 | 27000 | 7500 | 19500 |
| 14 | 9000 | 0 | 9000 |
| 15 | 9000 | 0 | 9000 |
| 13 | 10510 | 3003 | 7507 |
How to solve Dream11: Gross Winnings, Tax Withheld and Net Payout per User
The tax rule applies per winning, not per user: a user with two prizes of 6,000 owes nothing even though their total is 12,000. So the tax has to be worked out row by row and then summed — which is what conditional aggregation does. SUM(CASE WHEN amount > 10000 THEN amount * 30 / 100 ELSE 0 END) adds 30% of each large prize and 0 for every other one.
Gross winnings are a plain SUM(amount), and the net payout is gross minus tax. The comparison is strict, so a prize of exactly 10,000 is paid in full. Every amount is a multiple of 10, so the tax is a whole number and no rounding question arises.
The alternative computes the net directly, row by row, as 70% of a large prize or the whole of a small one, using integer DIV — the two forms agree because the arithmetic is exact. The order is by net payout descending with user_id breaking ties, so equal payouts still come out in a fixed order. One pass over the table, one group per user.
Reference solution (MySQL)
SELECT user_id,
SUM(amount) AS gross_winnings,
SUM(CASE WHEN amount > 10000 THEN amount * 30 / 100 ELSE 0 END) AS tds_deducted,
SUM(amount) - SUM(CASE WHEN amount > 10000 THEN amount * 30 / 100 ELSE 0 END) AS net_payout
FROM Winning
GROUP BY user_id
ORDER BY net_payout DESC, user_idAnother way
SELECT user_id, SUM(amount) AS gross_winnings,
SUM(IF(amount > 10000, amount * 3 DIV 10, 0)) AS tds_deducted,
SUM(IF(amount > 10000, amount * 7 DIV 10, amount)) AS net_payout
FROM Winning GROUP BY user_id ORDER BY net_payout DESC, user_id← Dream11: Depositors Who Never Joined a Contest · Dream11: Saved Teams Over the 100-Credit Cap →