Grouping Data Coding Practice Challenges | CodeNivo

Question 1

18. GROUP BY with Joins: A Preview

Accepted Answer

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY total_spent DESC, c.customer_id;

Answer

-- Practice query for Grouping Rows with GROUP BY

Question 2

10. Ratios Between Groups and Metrics

Accepted Answer

SELECT
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    ROUND(
        SUM(amount) / NULLIF(COUNT(*), 0),
        2
    ) AS average_amount_per_order
FROM orders
WHERE status = 'Completed'
GROUP BY category
ORDER BY average_amount_per_order DESC NULLS LAST;

Answer

-- Practice query for Combining Aggregates and Grouping

Question 3

13. Filtering and Sorting a Combined Report

Accepted Answer

SELECT
    category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales,
    ROUND(AVG(amount), 2) AS average_amount,
    MIN(amount) AS minimum_amount,
    MAX(amount) AS maximum_amount
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING COUNT(*) >= 5
   AND SUM(amount) >= 2000
ORDER BY total_sales DESC, category ASC;

Answer

-- Practice query for Combining Aggregates and Grouping

Question 4

5. Multi-Dimensional Reporting

Accepted Answer

SELECT
    department,
    category,
    COUNT(*) AS sale_line_count,
    SUM(quantity) AS units_sold,
    SUM(quantity * unit_price) AS total_revenue,
    ROUND(AVG(quantity * unit_price), 2) AS average_line_revenue,
    MIN(quantity * unit_price) AS smallest_line_revenue,
    MAX(quantity * unit_price) AS largest_line_revenue
FROM sales
GROUP BY department, category
ORDER BY total_revenue DESC NULLS LAST;

Answer

-- Practice query for Combining Aggregates and Grouping

Question 5

7. Combining WHERE and HAVING Correctly

Accepted Answer

SELECT
    category,
    COUNT(*) AS completed_order_count,
    SUM(amount) AS completed_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING COUNT(*) >= 5
   AND SUM(amount) >= 2000
ORDER BY completed_sales DESC;

Answer

-- Practice query for Understanding WHERE vs HAVING

Question 6

8. Combining Multiple Aggregate Functions

Accepted Answer

SELECT
    category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales,
    ROUND(AVG(amount), 2) AS average_order,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
GROUP BY category
ORDER BY total_sales DESC NULLS LAST;

Answer

-- Practice query for Grouping Rows with GROUP BY

Question 7

9. Combining WHERE, GROUP BY, and HAVING

Accepted Answer

SELECT
    category,
    COUNT(*) AS completed_order_count,
    SUM(amount) AS completed_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING SUM(amount) >= 1000
ORDER BY completed_sales DESC;

Answer

-- Practice query for Filtering Groups with HAVING

Question 8

17. GROUP BY with Calculated Values

Accepted Answer

SELECT
    CASE
        WHEN amount < 100 THEN 'Small'
        WHEN amount < 500 THEN 'Medium'
        ELSE 'Large'
    END AS order_size,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY
    CASE
        WHEN amount < 100 THEN 'Small'
        WHEN amount < 500 THEN 'Medium'
        ELSE 'Large'
    END
ORDER BY order_size;

Answer

-- Practice query for Grouping Rows with GROUP BY

customer_id	customer_name	order_count	total_spent
1	Asha	3	1500
2	Ravi	1	400
3	Neha	0	0

category	total_sales	order_count	average_amount_per_order
Electronics	1200	3	400.00
Books	600	2	300.00

department	category	sale_line_count	units_sold	total_revenue	average_line_revenue
Retail	Electronics	8	15	12000	1500.00
Online	Books	10	30	6000	600.00

category	completed_order_count	completed_sales
Electronics	8	4200
Furniture	5	2500

Grouping Data Problems