Query to join reactions with analytics data from paulie.dev

happy-cities.js

.select({
  city: analytics.city,
  country: analytics.country,
  flag: analytics.flag,
  count: sql`COUNT(${reactions.id})`.as('count'),
})
.from(analytics)
.innerJoin(reactions, eq(analytics.slug, reactions.slug))
.where(and(eq(reactions.reaction, 'happy'), gte(analytics.date, new Date(new Date().getTime() - 30 * 24 * 60 * 60 * 1000))))
.groupBy(analytics.city, analytics.country, analytics.flag)
.orderBy(desc(sql`count`))
.limit(50);

happy-cities.sql

WITH happy_reactions AS (
  SELECT analytics.city, analytics.country, analytics.flag, COUNT(reactions.id) AS count
  FROM analytics
  INNER JOIN reactions ON analytics.slug = reactions.slug
  WHERE reactions.reaction = 'happy' AND analytics.date >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY analytics.city, analytics.country, analytics.flag
)
SELECT city, country, flag, count
FROM happy_reactions
ORDER BY count DESC
LIMIT 50;