# Nested Aggregation And Filter Reasoning

> Skill: nested aggregation and filter reasoning

- Skill: `dingxingdi/nested-aggregation-and-filter-reasoning` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add dingxingdi/nested-aggregation-and-filter-reasoning`
- Raw SKILL.md: https://api.skillmd.com/api/skills/dingxingdi/nested-aggregation-and-filter-reasoning/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: dingxingdi (https://skillmd.com/u/dingxingdi)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/dingxingdi/nested-aggregation-and-filter-reasoning

---


# Skill: nested aggregation and filter reasoning

## 1. Capability Definition & Real Case
* **Professional Definition**: The ability to compose complex SQL programs that combine multi-level grouping, subquery-based relational comparisons, analytical window functions (e.g., RANK, LAG), counterfactual 'what-if' modeling via conditional expressions (CASE WHEN), and derived scalar mathematics to perform distributional or cross-cohort analysis.
* **Dimension Hierarchy**: Query Reasoning->Relational and Logical Composition->nested aggregation and filter reasoning

### Real Case
**[Case 1]**
* **Initial Environment**: An enterprise analytics environment containing global session and conversion data. The schema allows for high-volume grouping but requires derived scalar logic to isolate exact efficiency margins.
* **Real Question**: Find the page ID for the top three pages with the highest conversion rate, defined as the ratio of conversions to views, for the last month.
* **Real Trajectory**: Identify the group unit (page_id). Construct the numerator and denominator independently. Apply the mathematical division securely inside a derived projection and structure an ordered descending retrieval based strictly upon the generated rate metric.
* **Real Answer**: SELECT page_id, SUM(is_conversion)*1.0/NULLIF(COUNT(view_id), 0) AS conversion_rate FROM sessions WHERE timestamp >= '2024-12-01' GROUP BY page_id ORDER BY conversion_rate DESC LIMIT 3;
* **Why this demonstrates the capability**: The agent must span disjoint structural components of a synthesized mathematical ratio, deploying arithmetic safeguards and executing definitive positional ranking strictly upon an abstract derivation.
---
**[Case 2]**
* **Initial Environment**: A corporate database tracking sales performance includes an 'artist' table and a 'files' table that tracks size.
* **Real Question**: Is the average file size for the artist 'Coldplay' greater than the average file size for 'Radiohead'?
* **Real Trajectory**: Identify that two separate aggregations are needed for two different groupings. Compute scalar averages independently via symmetrical subqueries. Perform a relational comparison mapping between the aggregated components replacing a single entity lookup.
* **Real Answer**: SELECT (SELECT AVG(file_size) FROM files WHERE artist_name = 'Coldplay') > (SELECT AVG(file_size) FROM files WHERE artist_name = 'Radiohead');
* **Why this demonstrates the capability**: This tests processing relational comparison logic integrating two uniquely filtered subquery aggregates derived from the identical underlying architecture yielding a cross-cohort Boolean check natively.
---
**[Case 3]**
* **Initial Environment**: A movie metadata relational hierarchy comprising 'title_ratings' listing base averages and vote volumes. Simulation parameters require projecting 'virtual' value shifts.
* **Real Question**: Identify any movie in the current top 10 by average rating that would drop out of that list if we applied a penalty of -1.0 to the rating of any title with fewer than 5,000 votes.
* **Real Trajectory**: Retrieve the precise baseline grouping. Cultivate a virtual state modifier operating a dynamic CASE constraint conditionally adjusting target scores. Isolate displaced variants explicitly using rigorous set exclusions matching reality elements omitted natively from the penalized logic array.
* **Real Answer**: SELECT primaryTitle FROM title_basics JOIN (SELECT tconst FROM title_ratings ORDER BY averageRating DESC LIMIT 10) AS Reality ON title_basics.tconst = Reality.tconst EXCEPT SELECT primaryTitle FROM title_basics JOIN (SELECT tconst FROM title_ratings ORDER BY CASE WHEN numVotes < 5000 THEN averageRating - 1.0 ELSE averageRating END DESC LIMIT 10) AS Simulation ON title_basics.tconst = Simulation.tconst;
* **Why this demonstrates the capability**: Exhibits advanced conceptual what-if simulation embedded within layered SQL metrics dynamically altering specific values through localized conditionals compared securely against grounded actuals.

## Pipeline Execution Instructions
To synthesize data for this capability, you must strictly follow a 3-phase pipeline. **Do not hallucinate steps.** Read the corresponding reference file for each phase sequentially:

1. **Phase 1: Environment Exploration**
   Read the exploration guidelines to discover raw knowledge seeds:
   `references/EXPLORATION.md`

2. **Phase 2: Trajectory Selection**
   Once Phase 1 is complete, read the selection criteria to evaluate the trajectory:
   `references/SELECTION.md`

3. **Phase 3: Data Synthesis**
   Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data:
   `references/SYNTHESIS.md`