Top Jurisdictions Monthly SQL | Dataford Interview Questions

A tax analytics team needs a monthly report of transaction activity by jurisdiction. Write a SQL query to identify the top three jurisdictions by transaction volume for each month in the previous completed fiscal year, where the fiscal year begins on July 1.

Output

One row per ranked jurisdiction and month, with columns month_start, jurisdiction_name, transaction_volume, and monthly_rank.
Include only transactions dated within the previous completed fiscal year. Exclude transactions with a NULL date or without a matching jurisdiction.
Return at most three jurisdictions per month. Rank by transaction volume descending, breaking ties alphabetically by jurisdiction name.
Order by month_start, then monthly_rank, then jurisdiction_name.

Schema

tax_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
jurisdiction_id	INT	Tax jurisdiction associated with the transaction
transaction_date	DATE	Date on which the transaction occurred

tax_jurisdictions

Column	Type	Description
jurisdiction_idPK	INT	Unique tax jurisdiction identifier
jurisdiction_name	VARCHAR(100)	Name of the tax jurisdiction

Your question is Top Jurisdictions Monthly SQL. 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