Conditional Aggregation for Two Rows | Dataford Interview Questions

Write a single SQL query that returns exactly two rows containing specific aggregated metrics, using conditional aggregation or precise filtering instead of an unnecessary union.

Use the accounts and transactions tables. Return metrics for the pro and standard account tiers, excluding accounts with another or missing tier.

Output

One row for each tier, ordered pro first and standard second.
Columns: account_tier, posted_transaction_count, and posted_amount.
Count and sum only transactions whose status is posted; treat a missing matching amount as zero.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_tier	VARCHAR(20)	Account tier used for reporting

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(10,2)	Transaction amount

Your question is Conditional Aggregation for Two Rows. 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