Basic Windowing & Partitioning (1–20)
-- 1. Row number per row globally
SELECT employee_id, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;
-- 2. Rank salaries globally
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;
-- 3. Dense rank salaries globally
SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk FROM employees;
-- 4. Partitioned row number by department
SELECT employee_id, department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rn
FROM employees;
-- 5. Partitioned rank by department
SELECT employee_id, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 6. Partitioned dense_rank by department
SELECT employee_id, department_id,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank
FROM employees;
-- 7. NTILE into 4 salary quartiles
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- 8. Percent rank across entire dataset
SELECT employee_id, salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS pct_rank
FROM employees;
-- 9. CUME_DIST of salary
SELECT employee_id, salary,
CUME_DIST() OVER (ORDER BY salary DESC) AS cum_dist
FROM employees;
-- 10. First salary per department
SELECT employee_id, department_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_sal
FROM employees;
-- 11. Last salary per department
SELECT employee_id, department_id, salary,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sal
FROM employees;
-- 12. Lag salary by one row globally
SELECT employee_id, salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_sal
FROM employees;
-- 13. Lead salary by one row globally
SELECT employee_id, salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_sal
FROM employees;
-- 14. Moving average salary over 3 hires
SELECT employee_id, salary,
AVG(salary) OVER (ORDER BY hire_date ROWS 2 PRECEDING) AS mov_avg
FROM employees;
-- 15. Sum salary cumulative by department
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS dept_cum_sum
FROM employees;
-- 16. Minimum salary over window of 3 rows
SELECT employee_id, salary,
MIN(salary) OVER (ORDER BY hire_date ROWS 2 PRECEDING) AS min_win
FROM employees;
-- 17. Maximum salary over window of 5 rows
SELECT employee_id, salary,
MAX(salary) OVER (ORDER BY hire_date ROWS 4 PRECEDING) AS max_win
FROM employees;
-- 18. Count rows per department
SELECT employee_id, department_id,
COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;
-- 19. Row count overall
SELECT employee_id, COUNT(*) OVER () AS total_count FROM employees;
-- 20. Avg salary partitioned per job
SELECT employee_id, job_id, salary,
AVG(salary) OVER (PARTITION BY job_id) AS job_avg
FROM employees;
Intermediate Windowing with Ranges & Frames (21–40)
-- 21. CUME_DIST with default RANGE mode
SELECT employee_id, salary,
CUME_DIST() OVER (ORDER BY salary) AS dist
FROM employees;
-- 22. Running total by job
SELECT employee_id, job_id, salary,
SUM(salary) OVER (PARTITION BY job_id ORDER BY hire_date) AS job_run_sum
FROM employees;
-- 23. Difference between current and previous salary
SELECT employee_id, salary,
salary - LAG(salary) OVER (ORDER BY hire_date) AS diff_prev
FROM employees;
-- 24. Percentage change from previous salary
SELECT employee_id, salary,
(salary - LAG(salary) OVER (ORDER BY hire_date)) /
LAG(salary) OVER (ORDER BY hire_date) AS pct_change
FROM employees;
-- 25. Cumulative count of hired employees by month
SELECT hire_date,
COUNT(*) OVER (ORDER BY TRUNC(hire_date, 'MM')) AS cum_hires
FROM employees;
-- 26. Moving sum over last 30 days
SELECT hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date RANGE INTERVAL '30' DAY PRECEDING) AS last_30_days
FROM employees;
-- 27. Dense_rank partitioned per job and hire date
SELECT employee_id, job_id, salary,
DENSE_RANK() OVER (PARTITION BY job_id ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS d_rnk
FROM employees;
-- 28. Row number with exclusion of ties
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn
FROM employees;
-- 29. Weighted average salary over last 3 salaries (not ideal but illustrative)
SELECT employee_id, hire_date, salary,
SUM(salary * ROW_NUMBER() OVER (ORDER BY hire_date)) OVER (ORDER BY hire_date ROWS 2 PRECEDING) /
SUM(ROW_NUMBER() OVER (ORDER BY hire_date)) OVER (ORDER BY hire_date ROWS 2 PRECEDING) AS wavg
FROM employees;
-- 30. Running min salary globally
SELECT employee_id, salary,
MIN(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_min
FROM employees;
-- 31. Running max salary globally
SELECT employee_id, salary,
MAX(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_max
FROM employees;
-- 32. Percent rank within department
SELECT employee_id, department_id, salary,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS d_pct_rank
FROM employees;
-- 33. NTILE by department
SELECT employee_id, department_id, salary,
NTILE(3) OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_tile
FROM employees;
-- 34. First hire date per department repeated on all rows
SELECT employee_id, department_id, hire_date,
FIRST_VALUE(hire_date) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_hire
FROM employees;
-- 35. Last hire date per department
SELECT employee_id, department_id, hire_date,
LAST_VALUE(hire_date) OVER (PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire
FROM employees;
-- 36. Gap between current salary and department average
SELECT employee_id, department_id, salary,
salary - AVG(salary) OVER (PARTITION BY department_id) AS salary_gap
FROM employees;
-- 37. Count hires per quarter by department
SELECT department_id, TRUNC(hire_date, 'Q') AS qtr,
COUNT(*) OVER (PARTITION BY department_id, TRUNC(hire_date, 'Q')) AS hires_qtr
FROM employees;
-- 38. Moving average salary per department over 6 months
SELECT employee_id, department_id, salary, hire_date,
AVG(salary) OVER (PARTITION BY department_id
ORDER BY hire_date RANGE INTERVAL '6' MONTH PRECEDING) AS mov_avg_6m
FROM employees;
-- 39. Difference from department median (using PERCENTILE_CONT)
SELECT employee_id, department_id, salary,
salary - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS med_diff
FROM employees;
-- 40. Annual growth rate per employee vs last year's salary
WITH cte AS (
SELECT employee_id, hire_date, salary,
LAG(salary, 12) OVER (ORDER BY hire_date) AS prev_year_sal
FROM employees
)
SELECT employee_id, (salary - prev_year_sal) / prev_year_sal AS annual_growth
FROM cte WHERE prev_year_sal IS NOT NULL;
Advanced Analytics & Runs/Aperiods (41–60)
-- 41. Flag salary increases over previous record
SELECT employee_id, salary,
CASE WHEN salary > LAG(salary) OVER (ORDER BY hire_date) THEN 1 ELSE 0 END AS is_increase
FROM employees;
-- 42. Summed increases in streak
SELECT employee_id, hire_date, salary,
SUM(CASE WHEN salary > LAG(salary) OVER (ORDER BY hire_date) THEN 1 ELSE 0 END)
OVER (ORDER BY hire_date) AS increase_streak
FROM employees;
-- 43. Identify departments with median salary above threshold
SELECT DISTINCT department_id
FROM (
SELECT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS med_sal
FROM employees
)
WHERE med_sal > 7000;
-- 44. Window with ORDER BY salary NULLS LAST
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC NULLS LAST) AS rnk_salary
FROM employees;
-- 45. Running sum resetting by department and month
SELECT employee_id, department_id, salary, hire_date,
SUM(salary) OVER (PARTITION BY department_id, TRUNC(hire_date, 'MM')
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS monthly_cum_sum
FROM employees;
-- 46. First and last name concatenated list per department using LISTAGG and OVER
SELECT DISTINCT department_id,
LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name)
OVER (PARTITION BY department_id) AS emp_list
FROM employees;
-- 47. Calculate running count of high earners (salary > X)
SELECT employee_id, salary,
COUNT(*) OVER (ORDER BY hire_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FILTER (WHERE salary > 8000) AS high_earner_count
FROM employees;
-- 48. Rolling standard deviation over 12 rows
SELECT employee_id, salary,
STDDEV(salary) OVER (ORDER BY hire_date ROWS 11 PRECEDING) AS roll_stddev
FROM employees;
-- 49. Check salary trend per job - up/down pattern
SELECT employee_id, job_id, salary,
LAG(salary) OVER (PARTITION BY job_id ORDER BY hire_date) AS prev_job_sal,
CASE WHEN salary > LAG(salary) OVER (PARTITION BY job_id ORDER BY hire_date) THEN 'UP' ELSE 'DOWN' END AS trend
FROM employees;
-- 50. Windowed median salary across company
SELECT DISTINCT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS global_median
FROM employees;
-- 51. Aggregated range of hire dates per department
SELECT department_id,
MIN(hire_date) OVER (PARTITION BY department_id) AS dept_start,
MAX(hire_date) OVER (PARTITION BY department_id) AS dept_end
FROM employees;
-- 52. Assign group labels by salary quantile per department
SELECT employee_id, department_id, salary,
NTILE(4) OVER (PARTITION BY department_id ORDER BY salary) AS sal_quantile
FROM employees;
-- 53. Cumulative density of salary improvement by department
SELECT employee_id, department_id, salary,
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS dept_cum_dist
FROM employees;
-- 54. Percent difference from department average
SELECT employee_id, department_id, salary,
(salary - AVG(salary) OVER (PARTITION BY department_id)) /
AVG(salary) OVER (PARTITION BY department_id) AS pct_diff_avg
FROM employees;
-- 55. Running difference within department
SELECT employee_id, department_id, salary,
SUM(salary - AVG(salary) OVER (PARTITION BY department_id))
OVER (PARTITION BY department_id ORDER BY hire_date) AS dept_cum_diff
FROM employees;
-- 56. Check if salary ever dropped for employee sequence
SELECT employee_id, hire_date, salary,
CASE WHEN salary < LAG(salary) OVER (PARTITION BY employee_id ORDER BY hire_date) THEN 'DROPPED' END AS dropped
FROM employees;
-- 57. Identify consecutive high salary runs
SELECT employee_id, salary, hire_date,
MATCH_RECOGNIZE (
PARTITION BY employee_id
ORDER BY hire_date
MEASURES CLASSIFIER() AS seq_type, COUNT(*) AS run_length
PATTERN (H+)
DEFINE H AS salary > 10000
)
FROM employees;
-- 58. Quarterly moving sum per department
SELECT employee_id, department_id, salary, hire_date,
SUM(salary) OVER (
PARTITION BY department_id
ORDER BY TRUNC(hire_date,'Q')
RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW
) AS qtr_sum
FROM employees;
-- 59. Calculate annual salary rank within department
SELECT employee_id, department_id, salary,
RANK() OVER (
PARTITION BY department_id, EXTRACT(YEAR FROM hire_date)
ORDER BY salary DESC
) AS yr_dept_rank
FROM employees;
-- 60. Calculate 3-year moving average salary
SELECT employee_id, hire_date, salary,
AVG(salary) OVER (
ORDER BY TRUNC(hire_date,'YYYY')
RANGE BETWEEN INTERVAL '3' YEAR PRECEDING AND CURRENT ROW
) AS mov_avg_3yr
FROM employees;
Pattern Matching, MATCH_RECOGNIZE & RETURNING Clauses (61–80)
-- 61. Detect 2 consecutive salary increases
SELECT employee_id, salary, hire_date
FROM employees
MATCH_RECOGNIZE (
PARTITION BY employee_id
ORDER BY hire_date
MEASURES
FIRST(SAL) AS start_sal, LAST(SAL) AS end_sal
ONE ROW PER MATCH
PATTERN (UP UP)
DEFINE UP AS SAL > PREV(SAL)
);
-- 62. Label seasons by hire_date month
SELECT employee_id, hire_date,
CASE WHEN hire_date BETWEEN DATE '2021-12-21' AND DATE '2022-03-19' THEN 'Winter' END AS season
FROM employees
MATCH_RECOGNIZE (
...
);
-- 63. Find salary peaks: local maxima over window
SELECT employee_id, salary, hire_date
FROM employees
WHERE salary = MAX(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
-- 64. Use GROUPS frame: count over peer groups
SELECT employee_id, salary,
COUNT(*) OVER (ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS peer_cnt
FROM employees;
-- 65. Compute lagged differences using MATCH_RECOGNIZE
SELECT * FROM employees
MATCH_RECOGNIZE (
ORDER BY hire_date
MEASURES LAG(salary) AS prev_sal, salary - LAG(salary) AS diff
PATTERN (A B)
DEFINE A AS TRUE, B AS TRUE
);
-- 66. Return listagg over window of 5 rows
SELECT employee_id, salary,
LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY hire_date)
OVER (ORDER BY hire_date ROWS 4 PRECEDING) AS recent_5_names
FROM employees;
-- 67. Rolling median using PERCENTILE_CONT
SELECT employee_id, salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
OVER (ORDER BY hire_date ROWS 4 PRECEDING) AS rolling_median
FROM employees;
-- 68. Cumulative distribution within department window
SELECT employee_id, department_id, salary,
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS dept_cum_dist
FROM employees;
-- 69. Percentile rank with analytic function
SELECT employee_id, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;
-- 70. Count over UNBOUNDED PRECEDING clause
SELECT employee_id, salary,
COUNT(*) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS hires_to_date
FROM employees;
-- 71. Detect salary dips (current < min of last 3)
SELECT employee_id, salary,
CASE WHEN salary < MIN(salary) OVER (ORDER BY hire_date ROWS 3 PRECEDING) THEN 'dip' END AS dip_flag
FROM employees;
-- 72. Use GROUPS frame for peers around salary
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS grp_sum
FROM employees;
-- 73. Use MATCH_RECOGNIZE to detect three alternations
SELECT * FROM employees
MATCH_RECOGNIZE (
ORDER BY hire_date
PATTERN (UP DOWN UP)
DEFINE UP AS salary > PREV(salary),
DOWN AS salary < PREV(salary)
);
-- 74. Lead with IGNORE NULLS
SELECT employee_id, salary,
LEAD(salary, 1, 0) OVER (ORDER BY hire_date IGNORE NULLS) AS next_not_null
FROM employees;
-- 75. Lag with RESPECT NULLS
SELECT employee_id, salary,
LAG(salary, 1) OVER (ORDER BY hire_date RESPECT NULLS) AS prev_respect_null
FROM employees;
-- 76. Return global sorted list with window listagg
SELECT DISTINCT department_id,
LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY salary DESC)
OVER () AS all_emp_sorted
FROM employees;
-- 77. Window JSON aggregation example
SELECT department_id,
JSON_ARRAYAGG(employee_name ORDER BY salary)
OVER (PARTITION BY department_id) AS emp_json_list
FROM employees;
-- 78. Return salary with weight using NTH_VALUE reversed
SELECT employee_id, salary,
NTH_VALUE(salary, 1) OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_salary
FROM employees;
-- 79. Moving ratio to report
SELECT employee_id, salary,
RATIO_TO_REPORT(salary) OVER () AS salary_pct
FROM employees;
-- 80. Window LISTAGG with overflow truncation
SELECT department_id,
LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name)
OVER (PARTITION BY department_id) ON OVERFLOW TRUNCATE AS emp_list
FROM employees;
🧾 Aggregation over Groups with Analytics (81–100)
-- 81. Counting moves per department per year using GROUP BY ROLLUP
SELECT department_id, EXTRACT(YEAR FROM hire_date) AS yr,
COUNT(*) AS hires,
SUM(COUNT(*)) OVER (PARTITION BY department_id ORDER BY EXTRACT(YEAR FROM hire_date))
AS cum_hires
FROM employees
GROUP BY ROLLUP(department_id, EXTRACT(YEAR FROM hire_date));
-- 82. Global median salary using PERCENTILE_CONT analytics
SELECT DISTINCT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS med_all
FROM employees;
-- 83. Salary range per department via analytics
SELECT DISTINCT department_id,
MAX(salary) OVER (PARTITION BY department_id) -
MIN(salary) OVER (PARTITION BY department_id) AS sal_range
FROM employees;
-- 84. Top 3 salaries per department using ROW_NUMBER
SELECT employee_id, department_id, salary
FROM (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) WHERE rn <= 3;
-- 85. Hiring cohort size with cumulative
SELECT hire_date,
COUNT(*) AS hires_on_day,
SUM(COUNT(*)) OVER (ORDER BY hire_date) AS cumulative_hires
FROM employees GROUP BY hire_date;
-- 86. Earnings share vs department total
SELECT employee_id, department_id, salary,
salary / SUM(salary) OVER (PARTITION BY department_id) AS share
FROM employees;
-- 87. Detect outliers > mean + 2*stddev by department
WITH stats AS (
SELECT department_id,
AVG(salary) OVER (PARTITION BY department_id) AS avg_sal,
STDDEV(salary) OVER (PARTITION BY department_id) AS sd_sal
FROM employees
)
SELECT e.employee_id, e.department_id, e.salary
FROM employees e
JOIN stats s USING (department_id)
WHERE e.salary > s.avg_sal + 2*s.sd_sal;
-- 88. Label employees as top/bottom 10% in comp using percentile ranking
SELECT employee_id, salary,
CASE WHEN PERCENT_RANK() OVER (ORDER BY salary DESC) <= 0.1 THEN 'Top10%'
WHEN PERCENT_RANK() OVER (ORDER BY salary DESC) >= 0.9 THEN 'Bottom10%'
END AS bracket
FROM employees;
-- 89. Count hires per quarter with cumulative quarter hires
SELECT TRUNC(hire_date,'Q') AS qtr,
COUNT(*) AS hires,
SUM(COUNT(*)) OVER (ORDER BY TRUNC(hire_date,'Q')) AS cum_hires
FROM employees GROUP BY TRUNC(hire_date,'Q');
-- 90. Rolling median hire interval in weeks
SELECT employee_id, hire_date,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hire_date - LAG(hire_date) OVER (ORDER BY hire_date))
OVER () AS median_week_interval
FROM employees;
-- 91. Salary density by job category
SELECT job_id, salary,
DENSE_RANK() OVER (ORDER BY salary) AS sal_density_rank
FROM employees;
-- 92. First salary in tenure per department
SELECT employee_id, department_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS dept_first_salary
FROM employees;
-- 93. Last salary before exit assuming leave_date column exists
SELECT employee_id, salary,
LAST_VALUE(salary) OVER (PARTITION BY employee_id ORDER BY leave_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS leaving_salary
FROM employees;
-- 94. Minutes employees spent as top earner per department
SELECT employee_id, department_id, salary,
COUNT(*) OVER (PARTITION BY department_id ORDER BY hire_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS time_top
FROM (
SELECT employee_id, department_id, salary, hire_date,
CASE WHEN salary = MAX(salary) OVER (PARTITION BY department_id) THEN 1 END AS is_top
FROM employees
)
WHERE is_top = 1;
-- 95. Identify if salary peaked using MATCH_RECOGNIZE
SELECT * FROM employees
MATCH_RECOGNIZE (
PARTITION BY department_id
ORDER BY hire_date
MEASURES FIRST(salary) AS start_sal, LAST(salary) AS peak_sal
PATTERN (UP+ DOWN+)
DEFINE UP AS salary > PREV(salary), DOWN AS salary < PREV(salary)
);
-- 96. Ratio to report per job group using window aggregate
SELECT employee_id, job_id, salary,
RATIO_TO_REPORT(salary) OVER (PARTITION BY job_id) AS pct_of_job
FROM employees;
-- 97. Cumulative sum of bonuses using WINDOW clause
SELECT employee_id, bonus,
SUM(bonus) OVER w AS cum_bonus
FROM employees WINDOW w AS (PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
-- 98. 95th percentile salary per department
SELECT department_id,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id)
AS pct95_salary
FROM employees;
-- 99. Check salary incremental drops via MATCH_RECOGNIZE
SELECT * FROM employees
MATCH_RECOGNIZE (
PARTITION BY employee_id
ORDER BY hire_date
PATTERN (UP DOWN UP)
DEFINE UP AS salary > PREV(salary), DOWN AS salary < PREV(salary)
);
-- 100. Aggregate JSON output per department
SELECT department_id,
JSON_ARRAYAGG(JSON_OBJECT('id' VALUE employee_id, 'sal' VALUE salary))
OVER (PARTITION BY department_id) AS dept_emp_json
FROM employees;
No comments:
Post a Comment