ATS Snapshots Required for Both Reports: Merchants and Resellers

[ExpandedVenture]

Report for Resellers

select 
    x.id as product_id, 
    c.id as merchant_id, 
    e.id as affiliate_id,
    x.name as product_name, 
    c.name as merchant_name,
    e.name as affiliate_name, 
    v.signup_email, 
    sum(isnull(v.revenue,0)) as revenue,
    max(v.create_time) as last_event_time,
    dbo.fnTimeAgoDescription(max(v.create_time), getdate()) as timeago
from tracking_pixel_visit v with (nolock index(IX_tracking_pixel_visit__id_affiliate__signup_email))
join tracking_pixel x with (nolock) on x.id=v.id_tracking_pixel

-- reseller
join [user] e with (nolock) on (e.id=v.id_affiliate and e.id_client='AE5BAC9F-F0D2-4925-B154-6E9F3070BD8D')

-- merchant
join [user] u with (nolock) on u.id=x.id_user
join client c with (nolock) on c.id=u.id_client

where v.signup_email is not null
and v.id_affiliate is not null

group by x.id, c.id, e.id, x.name, c.name, e.name, v.signup_email
go