SELECT ls.*,
COALESCE(agg.upvotes,0) AS upvotes,
COALESCE(agg.downvotes,0) AS downvotes,
COALESCE(uv.value,0) AS user_vote,
(uss.id IS NOT NULL) AS is_saved
FROM launched_sites ls
LEFT JOIN (
SELECT site_id,
SUM(value = 1) AS upvotes,
SUM(value = -1) AS downvotes
FROM site_votes
GROUP BY site_id
) agg ON agg.site_id = ls.id
LEFT JOIN site_votes uv
ON uv.site_id = ls.id AND uv.user_id = :uid
LEFT JOIN user_saved_sites uss
ON uss.site_id = ls.id AND uss.user_id = :uid2 WHERE ls.country_iso2 IN (:co0) AND ls.region IN (:re0) AND ls.created_at <= :date_end AND ls.created_at >= :date_start AND ls.l1 IN(:l10) AND ls.l2 IN(:l20) ORDER BY ls.created_at DESC LIMIT :limit OFFSET :offset
New Website Launches — Past Week — Content & Media › Video & Streaming — NCR · PH | Website Launches - Website Launches