🏠 होम
बेंचमार्क
📊 सभी बेंचमार्क 🦖 डायनासोर v1 🦖 डायनासोर v2 ✅ टू-डू लिस्ट ऐप्स 🎨 रचनात्मक फ्री पेज 🎯 FSACB - अल्टीमेट शोकेस 🌍 अनुवाद बेंचमार्क
मॉडल
🏆 टॉप 10 मॉडल 🆓 मुफ्त मॉडल 📋 सभी मॉडल ⚙️ किलो कोड
संसाधन
💬 प्रॉम्प्ट लाइब्रेरी 📖 एआई शब्दावली 🔗 उपयोगी लिंक
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.