🏠 Home
Benchmark Hub
📊 All Benchmarks 🦖 Dinosaur v1 🦖 Dinosaur v2 ✅ To-Do List Applications 🎨 Creative Free Pages 🎯 FSACB - Ultimate Showcase 🌍 Translation Benchmark
Models
🏆 Top 10 Models 🆓 Free Models 📋 All Models ⚙️ Kilo Code
Resources
💬 Prompts Library 📖 AI Glossary 🔗 Useful Links
Advanced

SQL Time-Series Analysis & Gaps

#sql #data-analysis #window-functions #postgresql

Write a complex SQL query utilizing window functions to calculate retention and handle missing data gaps.

You are a Data Engineer working with a PostgreSQL database. You have a table 'user_activity' containing user_id, action_type, and timestamp (which may have gaps). Write a single SQL query that performs the following: 1. Calculates the 7-day rolling retention rate for users who signed up in the last 30 days. 2. Identifies users who had a 'gap' of inactivity lasting at least 14 days, then returned. 3. Uses 'generate_series' to fill in missing days with zero counts to ensure the time-series data is continuous for visualization purposes. Explain the choice of window frames (ROWS vs RANGE) used in your query.