Database Essentials - Writing Advanced SQL Queries - Ranking and Filtering - Recap

Databases Postgres
1

In this article, we will dive deep into the concepts of ranking and filtering data in SQL. The video accompanying this article will provide a visual guide to understanding the content discussed here.

[Replace this text with the link to the video]

Key Concepts Explanation

Computing Daily Product Revenue

To compute the revenue generated by products on a daily basis, we need to join the orders and order_items tables, filter out orders with specific statuses, group the data by order date and product ID, and calculate the sum of order item subtotal. Here is an example query:

SELECT o.order_date,
       oi.order_item_product_id,
       round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date, oi.order_item_product_id
ORDER BY o.order_date, revenue DESC

Computing Rank for Daily Product Revenue

Once we have computed the daily product revenue, we can further rank the products for each date based on their revenue using the dense_rank() window function. Here is an example query to compute the rank:

SELECT nq.*,
    dense_rank() OVER (
        PARTITION BY order_date
        ORDER BY revenue DESC
    ) AS drnk
FROM (
    SELECT o.order_date,
        oi.order_item_product_id,
        round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue
    FROM orders o 
        JOIN order_items oi
            ON o.order_id = oi.order_item_order_id
    WHERE o.order_status IN ('COMPLETE', 'CLOSED')
    GROUP BY o.order_date, oi.order_item_product_id
) nq
ORDER BY order_date, revenue DESC

Filtering Data Based on Rank

After computing the rank for each product within each date, we can filter the data based on specific criteria. In this case, we filter the data to get the top 5 products by revenue for each day. Here is an example query for filtering the data:

SELECT * FROM (
    SELECT nq.*,
        dense_rank() OVER (
            PARTITION BY order_date
            ORDER BY revenue DESC
        ) AS drnk
    FROM (
        SELECT o.order_date,
            oi.order_item_product_id,
            round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue
        FROM orders o 
            JOIN order_items oi
                ON o.order_id = oi.order_item_order_id
        WHERE o.order_status IN ('COMPLETE', 'CLOSED')
        GROUP BY o.order_date, oi.order_item_product_id
    ) nq
) nq1
WHERE drnk <= 5
ORDER BY order_date, revenue DESC

Hands-On Tasks

  1. Compute the daily product revenue using the provided query.
  2. Compute the rank for each product within each date based on revenue.
  3. Filter the data to get the top 5 products by revenue for each day.

Conclusion

In this article, we explored the concepts of ranking and filtering data in SQL. By following the step-by-step guide and practicing the hands-on tasks, you will gain a better understanding of how to manipulate and analyze data effectively. Keep practicing and engaging with the community to enhance your SQL skills further.

Ranking and Filtering - Recap

Let us recap the procedure to get the top 5 products by revenue for each day.

Watch the video tutorial here