Approach
Relational aggregation
For Analyze Subscription Conversion , the query transforms and combines relational rows, then filters or aggregates them into the requested result.
- Identify the source rows and join keys.
- Apply filters before aggregation when possible.
- Group, rank, or project the final columns required by the result.
Code notes
- 30 lines of SQL from the credited upstream file 3497.sql.
- The implementation keeps its working state in language-native values and containers.
- No explicit loop blocks detected.
Complexity
Review join cardinality, grouping keys, and available indexes when estimating query cost.
Check the problem constraints before deciding whether this complexity will pass.
Use this to learn the idea, then write your own version.
1WITH2 FreeTrial AS (3 SELECT user_id, AVG(activity_duration) AS avg_free_trial_duration4 FROM UserActivity5 WHERE activity_type = 'free_trial'6 GROUP BY 17 ),8 Paid AS (9 SELECT user_id, AVG(activity_duration) AS avg_paid_duration10 FROM UserActivity11 WHERE activity_type = 'paid'12 GROUP BY 113 ),14 ConvertedUsers AS (15 SELECT DISTINCT FreeTrial.user_id16 FROM FreeTrial17 INNER JOIN Paid18 USING (user_id)19 )20SELECT21 ConvertedUsers.user_id,22 ROUND(FreeTrial.avg_free_trial_duration, 2) AS trial_avg_duration,23 ROUND(Paid.avg_paid_duration, 2) AS paid_avg_duration24FROM ConvertedUsers25INNER JOIN FreeTrial26 USING (user_id)27INNER JOIN Paid28 USING (user_id)29ORDER BY 1;30