SQL Explainer Examples

SQL query

SELECT
    product_id,
    name,
    price,
    stock_quantity
FROM
    products
WHERE
    category = 'Electronics'
    AND price > 500.00
    AND stock_quantity > 0
ORDER BY
    price DESC;

-- 1. Project four columns to return basic product details: identifier, display name, price and available quantity:
SELECT
    product_id,
    name,
    price,
    stock_quantity
-- 2. Source data comes from the products table:
FROM
    products
-- 3. Filter to only include items in the 'Electronics' category that cost more than 500.00 and have stock_quantity > 0 — i.e., high‑value electronics that are currently available for sale:
WHERE
    category = 'Electronics'
    AND price > 500.00
    AND stock_quantity > 0
-- 4. Sort results by price in descending order so the most expensive matching products appear first:
ORDER BY
    price DESC;

SQL query

SELECT
    u.username,
    COUNT(o.order_id) AS total_orders
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id
GROUP BY
    u.user_id, u.username
HAVING
    COUNT(o.order_id) > 3
ORDER BY
    total_orders DESC;

-- 1. Select the user's username and the aggregated count of their orders, aliased as total_orders:
SELECT
    u.username,
    COUNT(o.order_id) AS total_orders
-- 2. From the users table joined to orders using user_id; this is an inner join so only users with matching orders are included:
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id
-- 3. Group results by user (user_id and username) so the COUNT is per-user; HAVING filters to only include users with more than 3 orders:
GROUP BY
    u.user_id, u.username
HAVING
    COUNT(o.order_id) > 3
-- 4. Order the final result set by total_orders in descending order to list the most-active buyers first:
ORDER BY
    total_orders DESC;

SQL query

WITH ProductRevenue AS (
    SELECT
        product_id,
        SUM(quantity * price_at_purchase) AS total_revenue
    FROM
        order_items
    GROUP BY
        product_id
)
SELECT
    p.name,
    pr.total_revenue
FROM
    products p
JOIN
    ProductRevenue pr ON p.product_id = pr.product_id
ORDER BY
    pr.total_revenue DESC
LIMIT 5;

-- 1. Defines a CTE named ProductRevenue that calculates total_revenue per product by summing quantity * price_at_purchase from order_items:
WITH ProductRevenue AS (
    SELECT
        product_id,
        SUM(quantity * price_at_purchase) AS total_revenue
    FROM
        order_items
    GROUP BY
        product_id
)
-- 2. Main SELECT pulls the product name and the computed total_revenue from the products table joined to the CTE:
SELECT
    p.name,
    pr.total_revenue
FROM
    products p
-- 3. The JOIN is an inner join: only products that appear in order_items (have sales) will be included:
JOIN
    ProductRevenue pr ON p.product_id = pr.product_id
-- 4. Results are ordered by total_revenue descending to rank products by revenue:
ORDER BY
    pr.total_revenue DESC
-- 5. LIMIT 5 returns only the top five revenue-generating products; note that ties in revenue have no deterministic secondary ordering:
LIMIT 5;

SQL query

WITH RankedProducts AS (
    SELECT
        name,
        category,
        price,
        RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
    FROM
        products
)
SELECT
    name,
    category,
    price
FROM
    RankedProducts
WHERE
    price_rank <= 2;

-- 1. Defines a CTE named RankedProducts that will hold product rows augmented with a computed price_rank:
WITH RankedProducts AS (
-- 2. Selects name, category, price and computes price_rank using RANK() OVER (PARTITION BY category ORDER BY price DESC) — ranking products within each category by descending price (highest price gets rank 1):
    SELECT
        name,
        category,
        price,
        RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
-- 3. Reads rows from the products table to feed the window function; the CTE body ends here:
    FROM
        products
)
-- 4. Main query selects name, category, price from the CTE (RankedProducts):
SELECT
    name,
    category,
    price
FROM
    RankedProducts
-- 5. Filters to keep only the top two ranks per category (price_rank <= 2). Note: because RANK() is used, ties at a rank will include all tied rows and can return more than two products for a category:
WHERE
    price_rank <= 2;

SQL query

SELECT
    DATE_TRUNC('month', o.order_date)::date AS sales_month,
    SUM(oi.quantity * oi.price_at_purchase) AS monthly_revenue
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
WHERE
    o.order_date >= '2023-01-01' AND o.order_date < '2024-01-01'
GROUP BY
    sales_month
ORDER BY
    sales_month;

SELECT
    -- 1. DATE_TRUNC('month', o.order_date)::date creates the month bucket (first day of the month) used as sales_month; SUM(oi.quantity * oi.price_at_purchase) computes revenue using the recorded price at the time of purchase (protects against later price changes):
    DATE_TRUNC('month', o.order_date)::date AS sales_month,
    SUM(oi.quantity * oi.price_at_purchase) AS monthly_revenue
FROM
    -- 2. Inner JOIN between orders and order_items ensures only order items linked to an order are counted (orders without items are excluded). The join uses order_id to relate records:
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
WHERE
    -- 3. WHERE restricts to the calendar year 2023 using a half-open interval (>= '2023-01-01' AND < '2024-01-01'), which is index-friendly and avoids inclusive/exclusive endpoint ambiguity:
    o.order_date >= '2023-01-01' AND o.order_date < '2024-01-01'
    -- 4. GROUP BY aggregates revenue per sales_month and ORDER BY returns months in ascending order. Note: in PostgreSQL the SELECT alias (sales_month) is not visible to GROUP BY — use GROUP BY DATE_TRUNC('month', o.order_date)::date or GROUP BY 1 to avoid an error:
GROUP BY
    sales_month
ORDER BY
    sales_month;

SQL query

SELECT
    p.name AS product_name,
    u.username,
    r.comment,
    tag_element.tag
FROM
    reviews r
JOIN
    products p ON r.product_id = p.product_id
JOIN
    users u ON r.user_id = u.user_id
CROSS JOIN LATERAL
    jsonb_array_elements_text(r.metadata -> 'tags') AS tag_element(tag)
WHERE
    r.rating = 5
    AND r.metadata ->> 'verified_purchase' = 'true';

-- 1. Selected output columns: product name, reviewer username, the review text, and a single tag value (tag_element.tag):
SELECT
    p.name AS product_name,
    u.username,
    r.comment,
    tag_element.tag
-- 2. Base table is reviews (aliased r). It is joined to products and users to retrieve product name and reviewer username via the foreign keys r.product_id -> products.product_id and r.user_id -> users.user_id:
FROM
    reviews r
JOIN
    products p ON r.product_id = p.product_id
JOIN
    users u ON r.user_id = u.user_id
-- 3. CROSS JOIN LATERAL with jsonb_array_elements_text(r.metadata -> 'tags') expands the JSONB array stored at metadata->'tags' into one text row per tag. LATERAL lets the function reference the current review row (r), so each tag becomes its own output row:
CROSS JOIN LATERAL
    jsonb_array_elements_text(r.metadata -> 'tags') AS tag_element(tag)
-- 4. Filters to include only 5-star reviews that are marked as a verified purchase. Uses ->> to extract the JSON text value of metadata['verified_purchase'] and compares it to the string 'true':
WHERE
    r.rating = 5
    AND r.metadata ->> 'verified_purchase' = 'true';