1. Invoices per Country
A business is analyzing data by country. For each country, display the country name, total number of invoices, and their average amount. Format the average as a floating-point number with 6 decimal places. Return only those countries where their average invoice amount is greater than the average invoice amount over all invoices.
Schema
There are 4 tables: country, city, customer, invoice
Sample Data Tables
The average invoice amount is 2353.5. The average invoice amount of Country with ids 1, 2 and 3 are 4825, 1017.5 and 1218 respectively. Hence, the only country to report is Australia
SELECT t1.country_name
, COUNT(t4.id) as Total_Inv
, ROUNT(AVG(t4.total_price), 6) as Avg_Amt
FROM country t1
JOIN city t2 ON t1.id = t2.country_id
JOIN customer t3 ON t2.id = t3.city_id
JOINT invoice t4 ON t3.id = t4.customer_id
GROUP BY t1.country_name
HAVING ROUND(AVG(t4.total_price), 6) >
(
SELECT AVG(total_price)
FROM invoice
)2. Customer Spending
List all customers who spent 25% or less than the average amount spent on all invoices. For each customer, display their name and the amount spent to 6 decimal places. Order the result by the amount spent from high to low.
Schema
There are 2 tables: customer, invoice
Sample Data Tables
The average amount spent by all customers is 2353.5. The threshold is 25% of the average amount i.e. 588.375. The customer ids of interest are 3 and 4.
SELECT c.customer_name
, ROUND(SUM(i.total_price) *1.000000, 6)
FROM customer as c JOIN invoice as i ON c.id = i.customer_id
GROUP BY c.customer_name
HAVING SUM(i.total_price) <=
(
SELECT AVG(total_price)*0.25
FROM invoice
)
ORDER BY ROUND(SUM(i.total_price), 6) DESC3. Products Without Sales
Given the product and Invoice details for products at an online store, find all the products that were not sold. For each such products, display its SKU and product name. Order the result by SKU, ascending.
Schema
There are 2 tables: PRODUCT, INVOICE_ITEM
Sample Data Samples
Product ID’s 1, 2, 4, 5, 7 and 10 had sales. Product ID’s 3, 6, 8 and 9 did not. The expected return is:
330122 Rose Deep Hydration - FRESH
330125 Slice of Glow - GLOW RECIPE
330127 Power Pair! - IT COSMETICS
330128 Dewy Skin Mist - TATCHAMy Solution
Option 1: Using LEFT JOIN and remove NULL values
SELECT p.sku as SKU,
p.product_name as ProductName
FROM PRODUCT as p
LEFT JOIN INVOICE_ITEM as i
ON p.id = i.product_id
WHERE 1 = 1
AND i.invoice_id IS NULLOption 2: Using NOT IN
SELECT p.sku as SKU,
p.product_name as ProductName
FROM PRODUCT as p
WHERE 1 = 1
AND p.id NOT IN
(SELECT i.product_id
FROM INVOICE_ITEM i)
ORDER BY p.sku ASC4. Product Sales per City
For each pair of city and products, return the names of the city and product, as well the total amount spent on the product to 2 decimal places. Order the result by the amount spent from high to low then by city name and product name in ascending order.
Schema
There are 5 tables: customer, city, invoice, invoice_item, product
Sample Data Tables
The expected return is:
Wien Silk Pillowcase - SLIP 9500.00
London Game Of Thrones - URBAN DECAY 1300.00
London Capture Youth - DIOR 1000.00
Berlin Advanced Night Repair - ESTÉE LAUDER 950.00
Berlin Capture Youth - DIOR 400.00
Hamburg Silk Pillowcase - SLIP 360.00
Berlin Game Of Thrones - URBAN DECAY 325.00
Wien Pore-Perfecting Moisturizer - TATCHA 150.00
London Healthy Skin - KIEHL S SINCE 1851 136.00 My Solution
Should read careful the request and start simple by JOIN all the tables.
Tips
SELECT t1.city_name
, t5.product_name
, ROUND(SUM(t4.line_total_price), 2)
FROM city t1
JOIN customer t2 ON t1.id = t2.city_id
JOIN invoice t3 ON t2.id = t3.customer_id
JOIN invoice_item t4 ON t3.id = t4.invoice_id
JOIN product t5 ON t4.product_id = t5.id
GROUP BY t1.city_name, t5.product_name
ORDER BY SUM(t4.line_total_price), t1.city_name, t5.product_name ASCFollow me on:
Youtube: https://www.youtube.com/c/CarrY4U_VN
Group mới toanh cho GenZY học Data:
Page Facebook:
Tiktok:
