Ranking every Texas bar by alcohol sales with one SoQL query

Texas requires every mixed-beverage permit holder to report monthly liquor, wine, beer and cover-charge receipts. The Comptroller publishes all of it on data.texas.gov (dataset naix-2893). That's millions of rows going back years, one row per location per month.

The raw data answers "what did venue X report in March?" What people actually want to know is "who are the biggest and fastest-growing venues in my market, and who just opened?" Here's how to get that without downloading the dataset.

Trailing 12 months + prior 12 months in one query

SoQL supports case() inside aggregates, so you can compute several windows in one grouped pass:

$select=taxpayer_number, location_number,
  max(location_name) as v_name, max(location_city) as v_city,
  sum(case(obligation_end_date_yyyymmdd > '2025-08-31', total_receipts, true, 0)) as t12,
  sum(case(obligation_end_date_yyyymmdd <= '2025-08-31', total_receipts, true, 0)) as p12
$where=obligation_end_date_yyyymmdd between '2024-09-30' and '2026-08-31'
$group=taxpayer_number, location_number
$order=t12 DESC

YoY is just t12 / p12 - 1. A venue with p12 = 0 and recent first months is "new". One with zero receipts in the latest three months is "possibly closed".

Gotcha 1: alias collisions

If you write max(location_name) as location_name and later filter $where=location_name like ..., Socrata reads the alias as an aggregate in an ungrouped context and fails. Prefix your aliases (v_name).

Gotcha 2: the newest month is partial

Filers report late, so the latest month on the portal is usually incomplete. I pick the newest month whose row count is at least 90% of the busiest of the last six months.

Results (Sep 2025 – Aug 2026)

• Houston $1.57B, Dallas $1.22B, Austin $1.16B, San Antonio $0.83B, Fort Worth $0.46B
• Largest single venue: AT&T Stadium (Arlington), $38.3M
• Lubbock's #1: United Supermarkets Arena, $7.8M

Packaged version

I wrapped this (plus city ranks, new-venue and closed-venue flags, and a raw monthly mode) as an Apify Actor so non-developers can run it from a form or schedule it: https://apify.com/lonestar-leads/texas-bar-restaurant-sales. A companion Actor tracks new Texas businesses daily, including new LLCs: https://apify.com/lonestar-leads/texas-new-business-leads.

Data: Texas Comptroller of Public Accounts via data.texas.gov. Figures are gross receipts as reported, not profit.