Essential session storage is required to sign in. Optional analytics remain off unless you allow them.
Given a dataset (orders/events/transactions), write a suite of analytical queries that use Common Table Expressions for readability and WINDOW functions for cross-row analysis: running totals, per-group rankings (ROW_NUMBER/RANK), period-over-period comparison (LAG/LEAD), and percentage-of-total. Each query must return verifiably correct results and avoid collapsing rows where a window is needed.
Correct OVER/PARTITION BY/ORDER BY; running totals, rankings, and LAG/LEAD compute as intended without GROUP BY collapsing.
CTEs (WITH) used to compose readable, layered queries; logic is clear, not one giant nested query.
Results verified against expected values; edge cases (ties, NULLs, empty partitions) handled.
Queries are reasonable; no needless self-joins where a window function is the right tool.
Use this README structure to make the work reviewable.
DevPath fetches a read-only, size-capped snapshot. Submitted code is never executed.
# An analytics query suite with CTEs and window functions ## Goal Given a dataset (orders/events/transactions), write a suite of analytical queries that use Common Table Expressions for readability and WINDOW functions for cross-row analysis: running totals, per-group rankings (ROW_NUMBER/RANK), period-over-period comparison (LAG/LEAD), and percentage-of-total. Each query must return verifiably correct results and avoid collapsing rows where a window is needed. ## Acceptance criteria - [ ] Window functions: Correct OVER/PARTITION BY/ORDER BY; running totals, rankings, and LAG/LEAD compute as intended without GROUP BY collapsing. - [ ] CTEs & structure: CTEs (WITH) used to compose readable, layered queries; logic is clear, not one giant nested query. - [ ] Correctness: Results verified against expected values; edge cases (ties, NULLs, empty partitions) handled. - [ ] Performance awareness: Queries are reasonable; no needless self-joins where a window function is the right tool. ## Verification - [ ] Document installation and run commands. - [ ] Record automated checks and their exact commands. - [ ] Add representative output, screenshots, or a short demo where useful. - [ ] Confirm failure paths and known constraints. ## Decision log ### Decision title - Context: - Choice: - Alternatives considered: - Tradeoffs: ## Evidence - Link each rubric criterion to the file, test, or artifact that demonstrates it. ## Known limitations - List what is intentionally out of scope and what should be improved next.