docs/sql_examples.md

SQL Example Query Pack

1 Top 10 stands by scans/downloads/high-intent

SELECT
  v.fair_id,
  v.stand_code,
  v.exhibitor_name,
  v.stand_category,
  v.stand_taps,
  v.brochure_requests,
  v.exhibitor_scans,
  v.high_intent_unique_students,
  (v.stand_taps + v.brochure_requests + v.exhibitor_scans) AS interaction_score
FROM vw_stand_popularity v
WHERE v.fair_id = ?
ORDER BY interaction_score DESC, v.high_intent_unique_students DESC
LIMIT 10;

2 Top conferences

SELECT
  fair_id,
  conference_code,
  conference_title,
  topic,
  speaker_name,
  conference_scans,
  unique_attendees
FROM vw_conference_popularity
WHERE fair_id = ?
ORDER BY conference_scans DESC, unique_attendees DESC
LIMIT 10;

3 Lead detail with score breakdown and drivers

SELECT
  v.fair_id,
  v.student_id,
  v.full_name,
  v.email,
  v.intent_score,
  v.engagement_score,
  v.monetisability_score,
  v.richness_score,
  v.total_score,
  v.prequalified,
  v.tier_eur,
  v.resell_factor,
  v.monetisable_value_eur,
  v.fields_interest_count,
  v.stands_tapped_count,
  v.unique_stand_categories,
  v.exhibitor_scans_count,
  v.followup_cta_to_exhibitor,
  v.consent_partners,
  v.consent_call,
  CASE WHEN v.programme_of_interest IS NOT NULL THEN 1 ELSE 0 END AS programme_interest_present
FROM vw_lead_overview v
WHERE v.fair_id = ?
  AND v.student_id = ?;

4 KPI summary for fair dashboard

SELECT
  kd.kpi_date,
  kd.total_students,
  kd.checked_in_students,
  kd.stand_taps,
  kd.conference_scans,
  kd.exhibitor_scans,
  kd.post_fair_email_clicks,
  ROUND(kd.partner_optin_rate * 100, 2) AS partner_optin_pct,
  ROUND(kd.call_optin_rate * 100, 2) AS call_optin_pct,
  kd.total_monetisable_value_eur
FROM kpi_daily kd
WHERE kd.fair_id = ?
ORDER BY kd.kpi_date;

5 Total monetisable value and by tier

SELECT fair_id, total_monetisable_value_eur, avg_value_eur, leads_count
FROM vw_total_monetisable_value
WHERE fair_id = ?;

SELECT fair_id, tier_eur, leads_count, total_value_eur
FROM vw_tier_distribution
WHERE fair_id = ?
ORDER BY tier_eur DESC;