ORDER BY 
  (
    (
      COALESCE(recent_votes_count, 0)* 5 + COALESCE(recent_comments_count, 0) * 3
    )-1
  ) / pow(
    (
      EXTRACT(
        EPOCH 
        FROM 
          current_timestamp - created_at
      )/ 3600
    ) + 2, 
    1.4
  ) DESC

SELECT 
  * 
FROM 
  (
    WITH agg_tags AS (
      SELECT 
        post_id, 
        ARRAY_REMOVE(
          ARRAY_AGG(tags.slug), 
          NULL
        ) as tags 
      FROM 
        post_tags 
        INNER JOIN tags ON tags.ID = post_tags.TAG_ID 
        AND tags.tenant_id = post_tags.tenant_id 
      WHERE 
        post_tags.tenant_id = $1 
        AND tags.is_public = true 
      GROUP BY 
        post_id
    ), 
    agg_comments AS (
      SELECT 
        post_id, 
        COUNT(
          CASE WHEN comments.created_at > CURRENT_DATE - INTERVAL '30 days' THEN 1 END
        ) as recent, 
        COUNT(*) as all 
      FROM 
        comments 
        INNER JOIN posts ON posts.id = comments.post_id 
        AND posts.tenant_id = comments.tenant_id 
      WHERE 
        posts.tenant_id = $1 
        AND comments.deleted_at IS NULL 
      GROUP BY 
        post_id
    ), 
    agg_votes AS (
      SELECT 
        post_id, 
        COUNT(
          CASE WHEN post_votes.created_at > CURRENT_DATE - INTERVAL '30 days' THEN 1 END
        ) as recent, 
        COUNT(*) as all 
      FROM 
        post_votes 
        INNER JOIN posts ON posts.id = post_votes.post_id 
        AND posts.tenant_id = post_votes.tenant_id 
      WHERE 
        posts.tenant_id = $1 
      GROUP BY 
        post_id
    ) 
    SELECT 
      p.id, 
      p.number, 
      p.title, 
      p.slug, 
      p.description, 
      p.created_at, 
      COALESCE(agg_s.all, 0) as votes_count, 
      COALESCE(agg_c.all, 0) as comments_count, 
      COALESCE(agg_s.recent, 0) AS recent_votes_count, 
      COALESCE(agg_c.recent, 0) AS recent_comments_count, 
      p.status, 
      u.id AS user_id, 
      u.name AS user_name, 
      u.email AS user_email, 
      u.role AS user_role, 
      u.status AS user_status, 
      u.avatar_type AS user_avatar_type, 
      u.avatar_bkey AS user_avatar_bkey, 
      p.response, 
      p.response_date, 
      r.id AS response_user_id, 
      r.name AS response_user_name, 
      r.email AS response_user_email, 
      r.role AS response_user_role, 
      r.status AS response_user_status, 
      r.avatar_type AS response_user_avatar_type, 
      r.avatar_bkey AS response_user_avatar_bkey, 
      d.number AS original_number, 
      d.title AS original_title, 
      d.slug AS original_slug, 
      d.status AS original_status, 
      COALESCE(agg_t.tags, ARRAY[] :: text[]) AS tags, 
      COALESCE(null, false) AS has_voted 
    FROM 
      posts p 
      INNER JOIN users u ON u.id = p.user_id 
      AND u.tenant_id = $1 
      LEFT JOIN users r ON r.id = p.response_user_id 
      AND r.tenant_id = $1 
      LEFT JOIN posts d ON d.id = p.original_id 
      AND d.tenant_id = $1 
      LEFT JOIN agg_comments agg_c ON agg_c.post_id = p.id 
      LEFT JOIN agg_votes agg_s ON agg_s.post_id = p.id 
      LEFT JOIN agg_tags agg_t ON agg_t.post_id = p.id 
    WHERE 
      p.status != 6 
      AND p.tenant_id = $1 
      AND p.status = ANY($2)
  ) AS q 
WHERE 
  1 = 1 
ORDER BY 
  (
    (
      COALESCE(recent_votes_count, 0)* 5 + COALESCE(recent_comments_count, 0) * 3
    )-1
  ) / pow(
    (
      EXTRACT(
        EPOCH 
        FROM 
          current_timestamp - created_at
      )/ 3600
    ) + 2, 
    1.4
  ) DESC 
LIMIT 
  30;