🏠 Beranda
Benchmark
📊 Semua Benchmark 🦖 Dinosaurus v1 🦖 Dinosaurus v2 ✅ Aplikasi To-Do List 🎨 Halaman Bebas Kreatif 🎯 FSACB - Showcase Utama 🌍 Benchmark Terjemahan
Model
🏆 Top 10 Model 🆓 Model Gratis 📋 Semua Model ⚙️ Kilo Code
Sumber Daya
💬 Perpustakaan Prompt 📖 Glosarium AI 🔗 Tautan Berguna
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.