A Texas district's spending by category, latest year (search by district)

Custom SQL query returning more than 2,000 rows (hide)

WITH d AS (
  SELECT id, name FROM municipalities
  WHERE lower(name) LIKE '%' || lower(:district) || '%'
), y AS (
  SELECT muni_id, max(year) AS yr FROM school_finance
  WHERE muni_id IN (SELECT id FROM d) GROUP BY muni_id
)
SELECT d.name AS district, f.year, f.function_code, f.function_name, f.amount
FROM school_finance f
JOIN y ON y.muni_id = f.muni_id AND y.yr = f.year
JOIN d ON d.id = f.muni_id
ORDER BY f.amount DESC

Query parameters

district

Edit SQL