lol.sql

dayStatsQuery :: SubType -> SiteId -> TzOffset -> MySQLAction [DayStatsResult]
dayStatsQuery st siteId tzOffset = query [qc|
    SELECT dates.date,
           dates.day,
        CASE WHEN views IS NULL THEN 0 ELSE views END AS views,
        CASE WHEN clicks IS NULL OR redirects IS NULL THEN 0 ELSE
            clicks + redirects END AS clicks, -- ignored
        CASE WHEN views IS NULL OR clicks IS NULL OR redirects IS NULL THEN 0
            ELSE (clicks + redirects)/views END AS ctr,
        CASE WHEN revenue IS NULL THEN 0 ELSE revenue END AS revenue
    FROM
        (
            SELECT views.date, views.day, redirects, clicks, views,
                revenue
            FROM
            (
            -- views (threshold passed)
                SELECT DATE(CONVERT_TZ(FROM_UNIXTIME(threshold_stats.e_secs),
                        @@session.time_zone, ?)) AS date,                  /*1*/
                    DAYNAME(CONVERT_TZ(FROM_UNIXTIME(threshold_stats.e_secs),
                        @@session.time_zone, ?)) AS day,                   /*2*/
                    SUM(threshold_stats.value) as views,
                    SUM(bid_price * threshold_stats.value) AS revenue
                FROM threshold_stats
                INNER JOIN {ads}                                           /*3*/
                ON CAST(ads.ad_id AS CHAR) = threshold_stats.ad_id
                WHERE threshold_stats.ad_id <> 'None' AND
                    threshold_stats.threshold = 1
                GROUP BY date, day
                ORDER BY threshold_stats.e_secs DESC LIMIT 10
            ) AS views
            LEFT JOIN
            (
            -- redirects
                SELECT DATE(CONVERT_TZ(FROM_UNIXTIME(redirects_stats.time),
                        @@session.time_zone, ?)) AS date,                  /*4*/
                    DAYNAME(CONVERT_TZ(FROM_UNIXTIME(redirects_stats.time),
                        @@session.time_zone, ?)) AS day,                   /*5*/
                    SUM(redirects_stats.count) as redirects
                FROM redirects_stats
                INNER JOIN {ads}                                           /*6*/
                ON CAST(ads.ad_id AS CHAR) = redirects_stats.ad_id
                GROUP BY date, day
                ORDER BY redirects_stats.time DESC LIMIT 10
            ) AS redirects
            ON redirects.date = views.date AND
                redirects.day = views.day
            LEFT JOIN
            -- clicks
            (
                SELECT DATE(CONVERT_TZ(FROM_UNIXTIME(click_page_stats.time),
                        @@session.time_zone, ?)) AS date,                  /*7*/
                    DAYNAME(CONVERT_TZ(FROM_UNIXTIME(click_page_stats.time),
                        @@session.time_zone, ?)) AS day,                   /*8*/
                    SUM(click_page_stats.count) as clicks
                FROM click_page_stats
                INNER JOIN {ads}                                           /*9*/
                ON CAST(ads.ad_id AS CHAR) = click_page_stats.ad_id
                WHERE click_page_stats.click_type IN ('upvote', 'downvote',
                    'comment', 'fb_share', 'tumblr_share', 'twitter_share',
                    'reddit_share', 'stumble_share', 'email_share', 'other_share',
                    'google_share', 'pinterest_share') AND
                    click_page_stats.ad_id <> 'None' AND
                    click_page_stats.ad_id = click_page_stats.page_id
                GROUP BY date, day
                ORDER BY click_page_stats.time DESC LIMIT 10
            ) AS clicks
            ON views.date = clicks.date AND
                views.day = clicks.day
            GROUP BY views.date, views.day
            ORDER BY views.date, views.day LIMIT 10
        ) AS aggregate
    RIGHT JOIN {datesByDay}                                               /*10*/
    ON aggregate.date = dates.date
        AND aggregate.day = dates.day
    ORDER BY dates.date;
    |] (tzOffset, tzOffset, siteId,
        tzOffset, tzOffset, siteId,
        tzOffset, tzOffset, siteId,
        tzOffset)
    where
        ads = getAdSubqueries st
        datesByDay = datesByDaySubquery


datesByDaySubquery :: String
datesByDaySubquery = [qc|
    (
        SELECT DATE(a.date) as date, DAYNAME(a.date) as day
        FROM (
            SELECT CONVERT_TZ(NOW() - INTERVAL (ai.a + (10 * bi.a) +
                (100 * ci.a)) DAY, @@session.time_zone,
                ?) AS date                                          /*tzOffset*/
            FROM {generatedNumbersAiBiCi}
        ) a
        LIMIT 7
    ) AS dates
    |]


generatedNumbersAiBiCi :: String
generatedNumbersAiBiCi = [q| (
        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
        UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
        UNION ALL SELECT 9
    ) AS ai
    CROSS JOIN
    (
        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
        UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
        UNION ALL SELECT 9
    ) AS bi
    CROSS JOIN
    (
        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
        UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
        UNION ALL SELECT 9
    ) AS ci
    |]