THEN '1' WHEN n_cpl.pull_condition_id = 48 THEN '2' WHEN n_cpl.pull_condition_id = 49 THEN '3' ELSE 'other' END AS list, COUNT(DISTINCT n_c.id) AS contacts, COUNT(DISTINCT CASE WHEN service_start.contact_id IS NOT NULL THEN n_c.id ELSE NULL END) AS agreements, COUNT(DISTINCT CASE WHEN application_proxy.summarizable_id IS NOT NULL THEN n_c.id ELSE NULL END) AS agencies, COUNT(DISTINCT CASE WHEN cv.id IS NOT NULL THEN n_c.id ELSE NULL END) AS conversions FROM nkd.contact_pull_logs AS n_cpl INNER JOIN nkd.contacts AS n_c ON n_cpl.contact_id = n_c.id LEFT JOIN ( SELECT contact_id, staff_id, action_at FROM nkd.histories AS n_h INNER JOIN nkd.histories_history_actions AS n_hha ON n_h.id = n_hha.history_id WHERE n_hha.history_action_id = 1 AND n_h.history_result_id = 1 AND n_h.created_at >= '2017-12-19' ) AS service_start ON n_c.id = service_start.contact_id LEFT JOIN ( SELECT * FROM nkd.kpi_raw_data WHERE name = 'application_proxy' AND created_at >= '2017-12-19' ) AS application_proxy ON n_c.member_id = application_proxy.member_id LEFT JOIN ( SELECT id FROM nkd.applications WHERE recruit_state IN ('hire','pre_hire_confirm_admin') ) AS cv ON application_proxy.summarizable_id = cv.id WHERE n_cpl.pull_at >= '2017-12-19' GROUP BY dt, list ;