Miscellany

SELECT SUBSTR(t4.run, 3, 1), t4.entity, t4.imps imps4, t4.amount amount4, t3.imps imps3, t3.amount amount3
FROM (
    SELECT SUBSTR(pa.tag, 8, 4) run, entity.managing_entity_id entity, 
    SUM(pa.imps) imps, SUM(pa.pred_value) amount
    FROM entity
    INNER JOIN site ON site.publisher_entity_id = entity.id   
    INNER JOIN section ON section.site_id = site.id 
    INNER JOIN predict_analysis AS pa ON pa.section_id = section.id AND pa.tag LIKE 'time17_4_%'
    GROUP BY 1, 2
) AS t4
INNER JOIN (
    SELECT SUBSTR(pa.tag, 8, 4) run, entity.managing_entity_id entity, 
    SUM(pa.imps) imps, SUM(pa.pred_value) amount
    FROM entity
    INNER JOIN site ON site.publisher_entity_id = entity.id   
    INNER JOIN section ON section.site_id = site.id 
    INNER JOIN predict_analysis AS pa ON pa.section_id = section.id AND pa.tag LIKE 'time17_3_%'
    GROUP BY 1, 2
) AS t3 ON SUBSTR(t3.run, 2, 2) = SUBSTR(t4.run, 2, 2) AND t3.entity = t4.entity
ORDER BY 1, 4 DESC
;


SELECT SUBSTR(pa.tag, 10, 1) run, entity.managing_entity_id entity, 
    SUM(IF(pa.tag LIKE 'time17_4_%', pa.imps, 0)) imps4,
    SUM(IF(pa.tag LIKE 'time17_4_%', pa.pred_value, 0)) amount4,
    SUM(IF(pa.tag LIKE 'time17_3_%', pa.imps, 0)) imps3,
    SUM(IF(pa.tag LIKE 'time17_3_%', pa.pred_value, 0)) amount3
FROM entity
INNER JOIN site ON site.publisher_entity_id = entity.id   
INNER JOIN section ON section.site_id = site.id 
INNER JOIN predict_analysis AS pa ON pa.section_id = section.id AND pa.tag LIKE 'time17_%'
WHERE entity.managing_entity_id = 1
GROUP BY 1, 2
ORDER BY 1, 4 DESC
;