CTE Merchant Volume Share | Dataford Interview Questions

Write a SQL query to calculate the percentage of total transaction volume contributed by each merchant, utilizing a WITH statement (Common Table Expression).

Use the provided merchant and transaction records. Include merchants with no matching transactions, and calculate percentages from transactions associated with listed merchants.

Output

One row per merchant, including merchants with zero volume.
Columns: merchant_name, transaction_volume, and percentage_of_total_volume.
Round the percentage to two decimal places.
Order by percentage descending, then merchant name ascending.

Schema

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
merchant_id	INT	Merchant associated with the transaction
transaction_volume	DECIMAL(12,2)	Transaction monetary volume

Your question is CTE Merchant Volume Share. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem