Compléter Mastering SQL Aggregation ProblemsVersion en ligne Drills to master SQL aggregation problems par Good Sam 1 SELECT AS amount FROM total_sales sales 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 amount average_sale_amount FROM AVG SELECT AS 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 order_details SELECT FROM product_id COUNT distinct_products DISTINCT 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 MIN MAX SELECT FROM max_sale amount AS sales AS amount 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 YEAR ORDER sale_date FROM sale_year FROM sale_date EXTRACT amount YEAR sale_year sale_month FROM MONTH FROM BY GROUP total_sales EXTRACT sales AS EXTRACT BY AS FROM EXTRACT AS SELECT SUM sale_month sale_date sale_date MONTH 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