Skip to content

Add "new and returning attendees" report #11190

@nbudin

Description

@nbudin

We'd like a report that allows us to determine which attendees have never been to one of this organization's conventions before, and which have.

@dkapell wrote a SQL query that does this:

select u.id, u.email, count(ucp2.*) from users u
    inner join user_con_profiles ucp1 on u.id = ucp1.user_id
    inner join user_con_profiles ucp2 on u.id = ucp2.user_id
    inner join conventions c1 on c1.id = ucp1.convention_id
    inner join conventions c2 on c2.id = ucp2.convention_id
    inner join tickets t1 on t1.user_con_profile_id = ucp1.id
    inner join tickets t2 on t2.user_con_profile_id = ucp2.id
where 
    ucp1.convention_id = 205 and
    c1.organization_id = c2.organization_id
group by u.id
having count(ucp2.*) = 1

We should take this and turn it into a report page usable from the UI, hopefully using ActiveRecord syntax instead of raw SQL.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    Status

    Next

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions