Can you tell about cases when you used SQL queries, and describe examples of the most complex ones?

In my experience with SQL queries, I often worked with selections from multiple tables using JOINs, as well as with aggregations and subqueries. For example, one of the complex queries involved:

• Combining data from three tables (users, orders, products)
• Filtering by date and order status
• Grouping by users with a count of orders
• Using a subquery to get the maximum product price in each order

An example of such a query:

SELECT u.UserName, COUNT(o.OrderID) AS OrdersCount, SUM(oi.Quantity * oi.Price) AS TotalSpent
FROM Users u
JOIN Orders o ON u.UserID = o.UserID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2023-01-01' AND o.Status = 'Completed'
GROUP BY u.UserName
HAVING SUM(oi.Quantity * oi.Price) > (
    SELECT AVG(TotalPrice) FROM (
        SELECT SUM(Quantity * Price) AS TotalPrice
        FROM OrderItems
        GROUP BY OrderID
    ) AS OrderTotals
)
ORDER BY TotalSpent DESC;

This query allows you to get a list of users with the number and total of their orders, which exceed the average check, useful for analytics and marketing.