🏠 首页
基准测试
📊 所有基准测试 🦖 恐龙 v1 🦖 恐龙 v2 ✅ 待办事项应用 🎨 创意自由页面 🎯 FSACB - 终极展示 🌍 翻译基准测试
模型
🏆 前 10 名模型 🆓 免费模型 📋 所有模型 ⚙️ 🛠️ 千行代码模式
资源
💬 💬 提示库 📖 📖 AI 词汇表 🔗 🔗 有用链接
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.