Aggregate by Category and Date | Dataford Interview Questions

Synechron's Analytics Platform team tracks daily revenue across service categories. Write a PostgreSQL query that summarizes this activity by category and transaction date.

Requirements

Return one row for each category and transaction date combination.
Calculate the number of transactions and total revenue for each group, including groups where an individual revenue value is NULL.
Sort the output by category alphabetically and transaction date chronologically.

Schema

service_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
category	VARCHAR(50)	Synechron service category
transaction_date	DATE	Date of the transaction
revenue	DECIMAL(12,2)	Transaction revenue, which may be NULL

Your question is Aggregate by Category and Date. Start with the requirements and the one table 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

Synechron's Analytics Platform team tracks daily revenue across service categories. Write a PostgreSQL query that summarizes this activity by category and transaction date.

Requirements

Return one row for each category and transaction date combination.
Calculate the number of transactions and total revenue for each group, including groups where an individual revenue value is NULL.
Sort the output by category alphabetically and transaction date chronologically.

Schema

service_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
category	VARCHAR(50)	Synechron service category
transaction_date	DATE	Date of the transaction
revenue	DECIMAL(12,2)	Transaction revenue, which may be NULL

Your question is Aggregate by Category and Date. Start with the requirements and the one table 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.