1. InGo Help Center
  2. Reports & Tracking

Want Stats for Live Events Over Time?

If you need to run the monthly stats on how many shows are live per month, use the following SQL:

-- shows that went live each month
select date_trunc('month', live_date) month_live, count(*)
from (
select conference_id, min(confirmation_time) live_date
        from registration_data
        group by conference_id) conf_live
group by date_trunc('month', live_date)
order by month_live;

-- number of registrants per month
select date_trunc('month', confirmation_time) reg_month, count(*) reg_count
from registration_data
where confirmation_time is not null
group by date_trunc('month', confirmation_time)
order by reg_month;

-- number of advocates per month
select date_trunc('month', created_at) reg_month, count(*) adv_count
from attendee a
join users u on (a.user_c = u.id)
group by date_trunc('month', created_at)
order by reg_month;

-- number of shows in "Live" state per month
select date_trunc('month', theday) themonth,
count(distinct conference_id)
from (select theday at time zone 'GMT' theday from (select rownum id, timestamp with time zone '2012-12-31 00:00:00.00000 GMT' + rownum * interval '1 day'
theday from (select generate_series as rownum from generate_series(1, 100000)) zt_sequence) t where t.theday < timestamp with time zone '2015-10-01 00:00:00.00000 GMT') alldays
join (select conference_id, start_c end_date, min(confirmation_time) live_date
        from registration_data
        join conference on (conference.id = registration_data.conference_id)
        group by conference_id, start_c) conf_live on (alldays.theday between conf_live.live_date and conf_live.end_date)
group by date_trunc('month', theday)
order by themonth;