Wrong solution for Question 6 in step4.md #22
Description
The solution for Question 6 in step4.md is wrong.
Question - Summarise all buy and sell transactions for each member_id by generating 1 row for each member with the following additional columns:
- Bitcoin buy quantity
- Bitcoin sell quantity
- Ethereum buy quantity
- Ethereum sell quantity
Given Solution -
SELECT
member_id,
SUM(
CASE
WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
ELSE 0
END
) AS btc_buy_qty,
SUM(
CASE
WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
ELSE 0
END
) AS btc_sell_qty,
SUM(
CASE
WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
ELSE 0
END
) AS eth_buy_qty,
SUM(
CASE
WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
ELSE 0
END
) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;Issue - In the last case, the ticker is filtered with BTC, it should be ETH.
Correct solution -
SELECT
member_id,
SUM(
CASE
WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
ELSE 0
END
) AS btc_buy_qty,
SUM(
CASE
WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
ELSE 0
END
) AS btc_sell_qty,
SUM(
CASE
WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
ELSE 0
END
) AS eth_buy_qty,
SUM(
CASE
WHEN ticker = 'ETH' AND txn_type = 'SELL' THEN quantity
ELSE 0
END
) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;