51 monthly snapshots of every active US Federal Firearms Licensee, since 2022.

SELECT business_name, premise_city, license_expiration
FROM ffl_licenses
WHERE snapshot_month = '2024-06-01'
  AND license_type = '07'
  AND premise_state = 'TX'
  AND license_sequence NOT IN (
    SELECT license_sequence FROM ffl_licenses
    WHERE snapshot_month = '2024-03-01'
      AND license_type = '07' AND premise_state = 'TX'
  );

SELECT premise_state, COUNT(*) AS active_ffls
FROM ffl_licenses
WHERE snapshot_month = (SELECT MAX(snapshot_month) FROM ffl_licenses)
GROUP BY premise_state
ORDER BY active_ffls DESC;

SELECT is_sole_proprietor, COUNT(*) AS n
FROM ffl_licenses
WHERE snapshot_month = (SELECT MAX(snapshot_month) FROM ffl_licenses)
GROUP BY is_sole_proprietor;

WITH first_seen AS (
  SELECT license_sequence, license_region, license_district, license_type,
         MIN(snapshot_month) AS first_month
  FROM ffl_licenses GROUP BY 1,2,3,4
),
last_seen AS (
  SELECT license_sequence, license_region, license_district, license_type,
         MAX(snapshot_month) AS last_month
  FROM ffl_licenses GROUP BY 1,2,3,4
)
SELECT COUNT(*) AS churned_since_2022
FROM first_seen f JOIN last_seen l USING (license_sequence, license_region, license_district, license_type)
WHERE f.first_month = '2022-01-01'
  AND l.last_month < (SELECT MAX(snapshot_month) FROM ffl_licenses);

SELECT premise_zip, COUNT(*) AS active_ffls
FROM ffl_licenses
WHERE snapshot_month = (SELECT MAX(snapshot_month) FROM ffl_licenses)
GROUP BY premise_zip
ORDER BY active_ffls DESC
LIMIT 20;