SELECT
    adv_stat.*, adv_ad.*
FROM
    adv_ad
LEFT JOIN (
    SELECT
        COALESCE (
            adv_stat_day_ad.ad_id,
            adv_stat_day_goal.ad_id
        ) AS ad_id,
        SUM (adv_stat_day_ad.views) AS _views,
        SUM (adv_stat_day_ad.clicks) AS _clicks,
        SUM (adv_stat_day_ad.spent) AS _spent,
        SUM (adv_stat_day_goal.conversions) AS _conv,
        SUM (adv_stat_day_goal.revenue) AS _revenue,
        MAX (adv_stat_day_ad.last_update) AS last_update,
        CASE
    WHEN COALESCE (SUM(adv_stat_day_ad.spent), 0) != 0 THEN
        COALESCE (
            SUM (adv_stat_day_goal.revenue) :: NUMERIC / NULLIF (SUM(adv_stat_day_ad.spent), 0),
            0
        )
    END AS _roi,
    CASE
WHEN COALESCE (SUM(adv_stat_day_ad.views), 0) != 0 THEN
    COALESCE (
        SUM (adv_stat_day_ad.clicks) :: NUMERIC / NULLIF (SUM(adv_stat_day_ad.views), 0),
        0
    )
ELSE
    0
END AS _ctr,
 CASE
WHEN COALESCE (
    SUM (adv_stat_day_goal.conversions),
    0
) != 0 THEN
    COALESCE (
        SUM (adv_stat_day_goal.conversions) :: NUMERIC / NULLIF (SUM(adv_stat_day_ad.clicks), 0),
        0
    )
ELSE
    0
END AS _cvr,
 CASE
WHEN COALESCE (SUM(adv_stat_day_ad.views), 0) != 0 THEN
    1000 * COALESCE (
        SUM (adv_stat_day_ad.spent) :: NUMERIC / NULLIF (SUM(adv_stat_day_ad.views), 0),
        0
    )
ELSE
    0
END AS _cpm,
 CASE
WHEN COALESCE (SUM(adv_stat_day_ad.clicks), 0) != 0 THEN
    COALESCE (
        SUM (adv_stat_day_ad.spent) :: NUMERIC / NULLIF (SUM(adv_stat_day_ad.clicks), 0),
        0
    )
ELSE
    0
END AS _cpc,
 CASE
WHEN COALESCE (
    SUM (adv_stat_day_goal.conversions),
    0
) != 0 THEN
    COALESCE (
        SUM (adv_stat_day_ad.spent) :: NUMERIC / NULLIF (
            SUM (adv_stat_day_goal.conversions),
            0
        ),
        0
    )
ELSE
    0
END AS _cpa
FROM
    adv_stat_day_ad
FULL JOIN adv_stat_day_goal ON adv_stat_day_ad.ad_id = adv_stat_day_goal.ad_id
AND adv_stat_day_ad."day" = adv_stat_day_goal."day"
WHERE
    (
        adv_stat_day_goal.project_goal_id IS NULL
        OR adv_stat_day_goal.project_goal_id = 354
    )
AND (
    adv_stat_day_ad. DAY BETWEEN '2013-01-01' :: DATE
    AND 'TODAY' :: DATE
    OR adv_stat_day_ad. DAY IS NULL
)
AND (
    adv_stat_day_goal. DAY BETWEEN '2013-01-01' :: DATE
    AND 'TODAY' :: DATE
    OR adv_stat_day_goal. DAY IS NULL
)
GROUP BY
    COALESCE (
        adv_stat_day_ad.ad_id,
        adv_stat_day_goal.ad_id
    )
) AS adv_stat ON adv_stat.ad_id = adv_ad. ID
WHERE
    (adv_ad.status_loc != 99) AND adv_ad.project_id =  58
ORDER BY
    _views DESC NULLS LAST
LIMIT 25