Key Queries

Content Freshness by Scope

Find pages that haven’t been regenerated in 30 days:

SELECT s.name, COUNT(*) as stale_count
FROM pages p
JOIN scopes s ON s.id = p.scope_id
WHERE p.generated_at < datetime('now', '-30 days')
GROUP BY s.name;

SEO Gap Analysis

Find pages with missing or short meta descriptions:

SELECT url_path, title
FROM pages
WHERE meta_description IS NULL OR LENGTH(meta_description) < 50;

Listing Search by City

Find active listings in a specific city:

SELECT * FROM listings
WHERE city_id = (SELECT id FROM brazil_cities WHERE slug = 'sao-paulo' AND state_code = 'SP')
  AND status = 'active';

404 Resolution Status

Check how many 404 URLs have been resolved:

SELECT resolved, COUNT(*) FROM notfound_urls GROUP BY resolved;

Top Unresolved 404s

Find the most-hit unresolved URLs:

SELECT url_path, hit_count
FROM notfound_urls
WHERE resolved = 0
ORDER BY hit_count DESC
LIMIT 20;

Daily Rotation Status

Check which state was last refreshed:

SELECT * FROM daily_rotation_tracker ORDER BY refreshed_at DESC LIMIT 1;

Page Count by Type

SELECT pt.name, COUNT(*) as page_count
FROM pages p
JOIN page_types pt ON pt.id = p.page_type_id
GROUP BY pt.name;