sql 73 lines · 3 tabs

Database-Driven “Daily Top” with window functions

Shared by codesnips Jan 2026

3 tabs

CREATE TABLE daily_events (
    id          BIGSERIAL PRIMARY KEY,
    category    TEXT        NOT NULL,
    item_id     BIGINT      NOT NULL,
    score       NUMERIC(10,2) NOT NULL DEFAULT 0,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Supports both daily bucketing and per-category partitioning in the ranking query.
CREATE INDEX idx_daily_events_cat_time
    ON daily_events (category, occurred_at);

CREATE INDEX idx_daily_events_item
    ON daily_events (item_id);

WITH aggregated AS (
    SELECT
        date_trunc('day', occurred_at)::date AS day,
        category,
        item_id,
        SUM(score)   AS total_score,
        COUNT(*)     AS event_count
    FROM daily_events
    WHERE occurred_at >= now() - INTERVAL '30 days'
    GROUP BY 1, 2, 3
),
ranked AS (
    SELECT
        day,
        category,
        item_id,
        total_score,
        event_count,
        ROW_NUMBER() OVER (
            PARTITION BY day, category
            ORDER BY total_score DESC, event_count DESC, item_id ASC
        ) AS rn
    FROM aggregated
)
SELECT day, category, item_id, total_score, event_count, rn
FROM ranked
WHERE rn <= 5
ORDER BY day DESC, category ASC, rn ASC;

CREATE MATERIALIZED VIEW daily_top_items AS
WITH aggregated AS (
    SELECT
        date_trunc('day', occurred_at)::date AS day,
        category,
        item_id,
        SUM(score) AS total_score,
        COUNT(*)   AS event_count
    FROM daily_events
    GROUP BY 1, 2, 3
),
ranked AS (
    SELECT
        day, category, item_id, total_score, event_count,
        ROW_NUMBER() OVER (
            PARTITION BY day, category
            ORDER BY total_score DESC, event_count DESC, item_id ASC
        ) AS rn
    FROM aggregated
)
SELECT day, category, item_id, total_score, event_count, rn
FROM ranked
WHERE rn <= 5
WITH NO DATA;

-- Required for REFRESH ... CONCURRENTLY and enforces one row per slot.
CREATE UNIQUE INDEX idx_daily_top_slot
    ON daily_top_items (day, category, rn);

-- Run from a scheduled job, never inside a transaction block.
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_top_items;

3 files · sql Explain with highlit

This snippet builds a "daily top" leaderboard: for each category, the highest-ranked items per day, computed with SQL window functions instead of ad-hoc application code. The core problem is a top-N-per-group query, which naive SQL handles poorly — GROUP BY collapses rows and loses the detail, and correlated subqueries scan the table once per group. Window functions solve this cleanly by ranking rows within partitions while keeping every row available.

In schema.sql, daily_events is a raw fact table with one row per interaction, carrying category, item_id, a numeric score, and an occurred_at timestamp. The composite index on (category, occurred_at) supports both the daily bucketing and the per-category partitioning the ranking query relies on, so Postgres can avoid a full sort of the whole table.

The daily_top.sql tab is where the pattern lives. A CTE named aggregated first rolls the raw events up to one row per (day, category, item_id), summing score and counting events — this is the real grain the leaderboard cares about. The second CTE, ranked, applies ROW_NUMBER() OVER (PARTITION BY day, category ORDER BY total_score DESC, event_count DESC) to number items within each day-and-category group. ROW_NUMBER is chosen over RANK here so ties resolve to distinct positions and the top-N cut is exact; RANK/DENSE_RANK would be the choice if tied items should share a rank. The outer query keeps only rn <= 5, yielding a stable top-5 per category per day in a single pass.

Because recomputing this over a large fact table on every dashboard load is wasteful, daily_top_mv.sql wraps the same logic in a materialized view, daily_top_items. A UNIQUE index on (day, category, rn) is added deliberately: it enforces the one-row-per-slot invariant and, crucially, enables REFRESH MATERIALIZED VIEW CONCURRENTLY, which rebuilds the view without taking an exclusive lock so readers keep querying the old snapshot during refresh. The trade-off is staleness between refreshes and the storage cost of the duplicated rows, which is acceptable for a leaderboard refreshed on a schedule. A pitfall to watch: CONCURRENTLY requires that unique index and cannot run inside a transaction block, so the refresh is typically driven by a cron job or scheduled worker rather than inline request code.

Related snips

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

ruby

Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = false
    Bullet.bullet_logger = true
    Bullet.console = true

N+1 query detection with Bullet gem

rails performance activerecord

by Alex Kumar 2 tabs

ruby

# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord

by Sarah Mitchell 3 tabs

ruby

json.array! @posts do |post|
  json.cache! ['v1', post], expires_in: 1.hour do
    json.id post.id
    json.title post.title
    json.excerpt post.excerpt
    json.published_at post.published_at

Fragment caching for expensive JSON serialization

rails caching performance

by Alex Kumar 1 tab

ruby

class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

Postgres JSONB Partial Index for Feature Flags

rails postgres jsonb

by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Database-Driven “Daily Top” with window functions — share card

Link copied