Run it with:

recencyBoost.ts

import { sql } from 'bun';

async function fetch(categorySlug: string, sortBy: 'mostBenefitedFirst' | 'mostBenefitedLast') {
  const order = sortBy === 'mostBenefitedFirst' ? sql`ORDER BY diff DESC` : sql`ORDER BY diff ASC`;
  return await sql`
SELECT
  e.slug,
  es.reviewApprovedCountAggregation,
  es.ratingAggregation,
  SUM(r.rating * (1 + (730 - LEAST(DATEDIFF(CURDATE(), r.firstPublishedAt), 730)) / 730)) / SUM(1 + (730 - LEAST(DATEDIFF(CURDATE(), r.firstPublishedAt), 730)) / 730) AS boostedRating,
  SUM(r.rating * (1 + (730 - LEAST(DATEDIFF(CURDATE(), r.firstPublishedAt), 730)) / 730)) / SUM(1 + (730 - LEAST(DATEDIFF(CURDATE(), r.firstPublishedAt), 730)) / 730) - es.ratingAggregation AS diff
FROM review r
       INNER JOIN review_subject rs ON rs.reviewId = r.id
       INNER JOIN entry e ON e.id = rs.subjectId
       INNER JOIN tree_entry te ON te.id = e.id
       INNER JOIN entry_statistics es ON es.entryId = e.id
       INNER JOIN entry_category ec ON ec.entryId = e.id
       INNER JOIN tree_category tpmc ON tpmc.id = ec.categoryId
       INNER JOIN tree_category tc ON tc.rootId = tpmc.rootId AND tc.left <= tpmc.left AND tc.right >= tpmc.right
       INNER JOIN category c ON c.id = tc.id
WHERE te.level = 0 AND c.slug = ${categorySlug} AND es.reviewApprovedCountAggregation > 50 AND e.isDiscontinued = 0 AND r.approvalStatus = 'approved' AND r.isPublished = 1
GROUP BY e.id
${order}
LIMIT 10
  `;
}

async function main() {
  const categories = ['project-home-builders', 'internet-service-providers', 'coffee-machines', 'air-conditioners', 'health-insurance', 'insurance'];

  for (const category of categories) {
    const mostBenefitedListings = await fetch(category, 'mostBenefitedFirst');
    const leastBenefitedListings = await fetch(category, 'mostBenefitedLast');

    console.log(`Category: ${category}`);
    console.log('Most benefited listings:');
    console.table(mostBenefitedListings);
    console.log('Least benefited listings:');
    console.table(leastBenefitedListings);

    console.log('--------------------------------');
  }
}

main().catch(console.error);