Spaces:
Sleeping
Sleeping
File size: 2,111 Bytes
9e42d3b | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | # SQL Example Query Pack
## 1 Top 10 stands by scans/downloads/high-intent
```sql
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
```sql
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
```sql
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
```sql
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
```sql
SELECT fair_id, total_monetisable_value_eur, avg_value_eur, leads_count
FROM vw_total_monetisable_value
WHERE fair_id = ?;
```
```sql
SELECT fair_id, tier_eur, leads_count, total_value_eur
FROM vw_tier_distribution
WHERE fair_id = ?
ORDER BY tier_eur DESC;
```
|