codesnips
sql 21 lines · 1 tab

SQL window functions for feature extraction and behavioral ranking

Dr. Elena Vasquez Apr 2026
1 tab 
WITH ordered_events AS (
  SELECT
    customer_id,
    event_time,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_time DESC) AS event_rank,
    LAG(event_time) OVER (PARTITION BY customer_id ORDER BY event_time) AS previous_event_time,
    SUM(revenue) OVER (
      PARTITION BY customer_id
      ORDER BY event_time
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7_event_revenue
  FROM customer_events
)
SELECT
  customer_id,
  MAX(CASE WHEN event_rank = 1 THEN event_time END) AS latest_event_time,
  AVG(EXTRACT(EPOCH FROM (event_time - previous_event_time)) / 3600.0) AS avg_hours_between_events,
  MAX(rolling_7_event_revenue) AS max_recent_revenue
FROM ordered_events
GROUP BY customer_id;
1 file · sql Explain with highlit

A surprising amount of feature engineering is best done in SQL before Python ever runs. ROW_NUMBER, LAG, rolling windows, and partitioned aggregates are ideal for deriving customer behavior signals close to the source. I use SQL here when it reduces movement, ambiguity, and notebook-only logic.

Related snips

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 
class CreateTopSellersMv < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE MATERIALIZED VIEW top_sellers AS
        SELECT p.id            AS product_id,
               p.name          AS product_name,

Cache-Friendly “Top N” with Materialized View Refresh

rails postgres performance
by codesnips 4 tabs
ruby 
class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

Safe Raw SQL with exec_query + Binds

rails activerecord sql
by codesnips 2 tabs
go 
package store

import (
  "context"

  "github.com/jackc/pgx/v5"

Postgres transaction pattern with pgx: defer rollback, commit explicitly

go postgres pgx
by Leah Thompson 1 tab
go 
package store

import (
  "context"
  "database/sql"
)

sqlc transaction wrapper that keeps call sites clean

go sql postgres
by Leah Thompson 1 tab
ruby 
class Tag < ApplicationRecord
  has_many :taggings, dependent: :destroy

  scope :top, ->(limit = 20) {
    joins(:taggings)
      .group(Arel.sql("tags.id"))

Memory-Safe “top tags” aggregation with pluck + group

rails activerecord performance
by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

SQL window functions for feature extraction and behavioral ranking — share card
Link copied