Compléter Mastering SQL Aggregation ProblemsVersion en ligne Drills to master SQL aggregation problems par Good Sam 1 amount FROM SELECT total_sales sales AS SUM Problem 1 : Total Sales Question : You have a table sales ( order_id INT , amount DECIMAL ) . Write a SQL query to calculate the total sales amount . Solution : ( ) ; SELECT SUM ( amount ) AS total_sales FROM sales ; Explanation : The SUM ( ) function aggregates the total sales by summing up all the amount values in the sales table . 2 average_sale_amount AS FROM AVG SELECT amount sales Problem 2 : Average Sales per Order Question : Calculate the average sales amount per order from the sales table . Solution : ( ) ; SELECT AVG ( amount ) AS average_sale_amount FROM sales ; Explanation : The AVG ( ) function calculates the average amount of sales by dividing the total sum of the amount column by the count of rows ( i . e . , number of orders ) . 3 AS FROM distinct_products COUNT SELECT product_id DISTINCT order_details Problem 3 : Count of Distinct Products Sold Question : Given a table order_details ( order_id INT , product_id INT ) , find the number of distinct products sold . Solution : ( ) ; SELECT COUNT ( DISTINCT product_id ) AS distinct_products FROM order_details ; Explanation : COUNT ( DISTINCT product_id ) counts the unique product IDs in the order_details table , ignoring duplicates . 4 min_sale AS FROM amount SELECT MIN amount AS sales max_sale MAX Problem 4 : Maximum and Minimum Sale Question : Find both the largest and smallest sale amounts from the sales table . Solution : ( ) , ( ) ; SELECT MAX ( amount ) AS max_sale , MIN ( amount ) AS min_sale FROM sales ; Explanation : The MAX ( ) and MIN ( ) functions are used to find the highest and lowest values in the amount column of the sales table . 5 FROM YEAR SUM YEAR sale_year sale_month sale_date FROM FROM sale_month MONTH EXTRACT sales AS MONTH sale_date AS sale_date sale_year BY BY amount EXTRACT total_sales ORDER SELECT FROM EXTRACT EXTRACT FROM sale_date GROUP AS Problem 5 : Grouped Sales by Month Question : Assuming a table sales ( order_id INT , amount DECIMAL , sale_date DATE ) , calculate total sales for each month . Solution : ( ) , ( ) , ( ) ( ) , ( ) , ; SELECT EXTRACT ( YEAR FROM sale_date ) AS sale_year , EXTRACT ( MONTH FROM sale_date ) AS sale_month , SUM ( amount ) AS total_sales FROM sales GROUP BY EXTRACT ( YEAR FROM sale_date ) , EXTRACT ( MONTH FROM sale_date ) ORDER BY sale_year , sale_month ; Explanation : The SUM ( ) function is used to aggregate sales per month , grouped by year and month extracted from sale_date . The GROUP BY clause groups the data into sets that share the same year and month so that SUM ( ) can compute totals for each group .
Créer jeu