Retrieve User Details and Products They've Ordered Using INNER JOIN

SELECT
    u.user_id,
    u.username,
    p.product_name,
    p.price,
    oi.quantity
FROM
    users u
INNER JOIN
    orders o ON u.user_id = o.user_id
INNER JOIN
    order_items oi ON o.order_id = oi.order_id
INNER JOIN
    products p ON oi.product_id = p.product_id
WHERE
    u.is_active = TRUE
ORDER BY
    u.username, p.product_name;

How it works: This SQL snippet demonstrates a multi-table INNER JOIN, a fundamental operation for combining related data from several tables. It retrieves details about active users, their orders, the specific items in those orders, and the corresponding product information. The INNER JOIN clauses ensure that only rows with matching values in all joined tables are returned, providing a comprehensive view of what active users have purchased.