CTEs and window functions for analysis

Which months of 2026 were better than a typical month? Using only paid invoices dated in 2026, revenue for a month is the sum of amount. Return month ('YYYY-MM') and revenue for the months whose revenue is above the average monthly revenue of 2026, sorted by month from oldest to newest.

Finance tracks revenue year-to-date. For each month from January to September 2026 return three columns: month ('YYYY-MM'), that month's paid revenue (sum of amount of paid invoices), and the running total of paid revenue from January 2026 up to and including that month. Sort by month from oldest to newest.

Show how paid revenue moved from one month to the next. For each month from January to September 2026 return month ('YYYY-MM'), paid revenue, and the change versus the previous calendar month (this month's revenue minus last month's). January 2026 must be compared with December 2025, so its change is not empty. Sort by month from oldest to newest.

Rank customers by how much they paid in 2026. For each account with at least one paid invoice dated in 2026, return rank, company and total paid amount in 2026. Accounts with equal revenue share the same rank and the next rank skips numbers (1, 1, 3, …). Sort by rank, then company A to Z.

For a compensation review, list the two highest-paid employees in each department (just one if the department has only one person; departments without employees don't appear). Return department name, employee name, salary and their position within the department (1 = highest paid). Sort by department name A to Z, then salary highest first.

A customer who doesn't pay the very first invoice is an onboarding red flag. An account's first invoice is its earliest by invoice_date (if two share a date, the one with the lowest id). Return company, invoice_date and status of the first invoice, only for accounts whose first invoice is not 'paid'. Any row order is fine.

Where does the money come from? Using paid invoices dated in 2026, return country (of the account), paid revenue, and that country's share of total paid 2026 revenue as a percentage rounded to 1 decimal. Sort by revenue highest first, then country A to Z.

Are newer customers sticking around better than older ones? Group accounts into cohorts by the quarter of created_at, labelled like '2025-Q3' (Q1 = January to March). For each cohort return the cohort label, accounts created, accounts still active (churned_at is NULL), and the percentage still active rounded to 1 decimal. Sort by cohort from oldest to newest.