SQL: Top Companies by Revenue

Lumenalta needs a revenue leaderboard for fiscal year 2025. Write a PostgreSQL query that calculates each company's net settled revenue after refunds, ranks companies by revenue, and includes every company tied within the top three rank positions.

Requirements

1. Include only transactions with status = 'SETTLED' dated from 2025-01-01 through 2025-12-31.
2. Calculate net revenue as transaction gross amount minus all associated refunds. Treat missing refund totals as zero.
3. Include companies with no qualifying transactions, assigning them revenue of zero.
4. Use a tie-aware ranking method so ties are not arbitrarily truncated. Return all companies whose rank is at most 3.
5. Return company name, net revenue, and rank, ordered by rank ascending and company name ascending.

Representative Sample Data

companies: Aster Systems, BluePeak Labs, Cedar Health, Delta Freight, Ember Retail, Fathom Media, Lumenalta, Northstar Energy.

revenue_transactions: settled gross amounts include Aster transactions of 1000 and 500, BluePeak transactions of 900 and 600, Cedar transactions of 1200 and 300, Delta of 1499, Ember of 1300, and Lumenalta of 1600. A pending BluePeak transaction and an out-of-period Aster transaction must be excluded.

revenue_adjustments: Lumenalta has 100 in refunds, Ember has 100 in refunds, and Aster, BluePeak, and Cedar have zero or null adjustments. Include an adjustment for an unknown transaction to test join behavior.