Medidata request

gistfile1.sql

select 
e.author_id, 
u.name, 
ui.value as email,
u.roles, 
count(distinct e.parent_id) num_distinct_updates_wo_reassigment
from classic.events e
inner join users u on e.author_id = u.id
inner join user_identities ui on e.author_id = ui.user_id
where e.account_id = 361618 
and e.delta_date between 20160601 and 20160601  -- set date range
and ui.type like '%Email%'                   -- pull only email identities
and ui.priority = 1                          -- only look at the primary email address
and u.roles > 0                              -- only look at user's with an agent/admin role
and e.author_id != -1                        -- remove system updates
and e.parent_id is not null                  -- remove parent events
and e.type in ('Comment','Create','Change')  -- only look at events that are comments, creates, or updates
and e.value_reference != "group_id"          -- remove all group changes
and e.value_reference != "assignee_id"       -- remove all assignee changes
group by
e.author_id, 
u.name,
ui.value,
u.roles