services_with_planned_time.sql

SELECT 
    s.service_id, COUNT(s.service_id)
FROM
    service s
        LEFT JOIN
    (SELECT 
        s.service_id
    FROM
        service s
    JOIN broadcast b ON s.service_id = b.service_id
    WHERE
        b.planned_start_time IS NOT NULL
            AND DATE(b.created) > '2017-12-05'
            AND YEAR(b.published_time) > '2017') AS planned ON s.service_id = planned.service_id
        JOIN
    entity e ON e.pid = s.pid
        AND e.partner_pid = 's0000001'
WHERE
    s.type = 'TV'
GROUP BY s.service_id
ORDER BY COUNT(s.service_id) DESC