Top Spending Categories SQL

MediumSQL · PostgreSQL

Problem

Brex finance teams need a recent view of company spending by category. Write a PostgreSQL query that finds the top three categories for each company using net spend after refunds, evaluated as of 2025-03-31.

Requirements

Include transactions from the 30-day period from 2025-03-02 through 2025-03-31, inclusive.
Aggregate refunds by transaction before joining them to card transactions, because a transaction may have multiple refunds.
Treat missing refunds as zero and calculate net_spend as transaction amount minus refunded amount.
Aggregate net spend by company and category, then return the top three categories per company using a window function.
Include ties only when they fall within the first three row positions, and sort the final output by company name and descending net spend.

Schema

companies

Column	Type	Description
company_idPK	INT	Unique Brex customer company identifier
company_name	VARCHAR(120)	Company display name

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique card transaction identifier
company_id	INT	Company associated with the transaction
category	VARCHAR(80)	Spending category
amount	NUMERIC(12,2)	Original positive transaction amount
transaction_date	DATE	Date the transaction posted

refunds

Column	Type	Description
refund_idPK	INT	Unique refund identifier
transaction_id	INT	Transaction receiving the refund
amount	NUMERIC(12,2)	Refund amount
refunded_at	DATE	Date the refund posted

Tablescompaniescard_transactionsrefunds