Dream11: Gross Winnings, Tax Withheld and Net Payout per User

Easy
625.

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. 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

ColumnType
winning_idint
user_idint
contest_idint
amountint

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.

Example

The rows must come back in the order shown.

Winning

winning_iduser_idcontest_idamount
11170110000
21170215000
31270125000
413703500
51370410010
6147059000
7127062000
8157079000

Output

user_idgross_winningstds_deductednet_payout
1125000450020500
1227000750019500
14900009000
15900009000
131051030037507
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
winning_idint
user_idint
contest_idint
amountint

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_iduser_idcontest_idamount
11170110000
21170215000
31270125000
413703500
51370410010
6147059000
7127062000
8157079000

Output

user_idgross_winningstds_deductednet_payout
1125000450020500
1227000750019500
14900009000
15900009000
131051030037507

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_id

Another 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 →