🏠 Trang chủ
Benchmark
📊 Tất cả benchmark 🦖 Khủng long v1 🦖 Khủng long v2 ✅ Ứng dụng To-Do List 🎨 Trang tự do sáng tạo 🎯 FSACB - Trình diễn cuối cùng 🌍 Benchmark dịch thuật
Mô hình
🏆 Top 10 mô hình 🆓 Mô hình miễn phí 📋 Tất cả mô hình ⚙️ Kilo Code
Tài nguyên
💬 Thư viện prompt 📖 Thuật ngữ AI 🔗 Liên kết hữu ích
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.