What aggregate and window functions have you used in your work?.

In working with data, I often used the following aggregate and window functions:

Aggregate functions:

• SUM() — summing values within a group.
• COUNT() — counting the number of records.
• AVG() — calculating the average value.
• MIN() and MAX() — finding the minimum and maximum values.

Window functions:

• ROW_NUMBER() — numbering rows within a window.
• RANK() and DENSE_RANK() — ranking with consideration of ties.
• LAG() and LEAD() — accessing the previous or next value within a window.
• SUM() OVER (PARTITION BY ...) — aggregate sum with partitioning by groups without grouping the entire dataset.

Example of using a window function:

SELECT user_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

This allows analyzing cumulative metrics and trends in the data.