Conversion Index

SELECT
    '42' as AccountID,
    'default' as Segment,
    Station as Network,
    DayPart,
    Count(*) / Sum(TotalSessions) AS KPI1
    FROM (SELECT
        Session,
        Station,
        DayPart,
        StartDateTime as SpotMinute,
        TotalSessions,
        a.KPI1
    FROM (SELECT
        tn.id,
        tn.DayPart,
        CalendarDate,
        cml.KPI1,
        Minute,
        StartDateTime,
        Station,
        Sessions,
        LENGTH(Sessions) - LENGTH(REPLACE(Sessions, ',', '')) as TotalSessions
        FROM bvs.TwoNil_Commercial_Daily tn
        LEFT JOIN blueapron.SpotAttribution_ClientMinuteLevel cml
            ON (
                cml.Minute BETWEEN convert_tz(tn.StartDateTime, 'EST', 'America/New_York')
                        AND DATE_ADD(convert_tz(tn.StartDateTime, 'EST', 'America/New_York'), INTERVAL 10 MINUTE)
            )
        WHERE tn.AccountID = '42'
                AND CalendarDate BETWEEN convert_tz('2014-11-09', 'EST', 'America/New_York') AND convert_tz('2014-11-11', 'EST', 'America/New_York')
                AND StartDateTime BETWEEN convert_tz('2014-11-09 23:00:00', 'EST', 'America/New_York') AND convert_tz('2014-11-11 23:59:59', 'EST', 'America/New_York')
    ) AS a
        RIGHT JOIN blueapron.SpotAttribution_ClientSessionLevel b
            ON (
                b.Minute >= a.Minute
                AND b.Minute < DATE_ADD(a.Minute, INTERVAL 1 DAY)
            )
        WHERE FIND_IN_SET(b.Session, a.Sessions)
        GROUP BY a.id, Session
    ) AS x
GROUP BY Station, DayPart