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
)
SELECT
co.country_name,
COUNT(*) AS total_customers,
ROUND(AVG(i.total_price), 6) AS avg_total_price
FROM
country AS co
INNER JOIN city AS ci ON co.id = ci.country_id
INNER JOIN customer AS cu ON ci.id = cu.city_id
INNER JOIN invoice AS i ON cu.id = i.customer_id
GROUP BY
co.country_name
HAVING
AVG(i.total_price) > (SELECT AVG(total_price) FROM invoice);
SELECT
co.country_name, count(*), AVG(i.total_price)
FROM
country co, city ci, customer cu, invoice i
WHERE
co.id = ci.country_id AND ci.id = cu.city_id AND cu.id = i.customer_id
GROUP BY
co.country name
HAVING
AVG(i.total_price) > (SELECT AVG(total price));
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) DESC
3. 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 - TATCHA
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 NULL
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 ASC
SELECT
product.sku, product.product_name
FROM
product
WHERE
product.id NOT IN (SELECT product_id FROM invoice_item);
SELECT
product.sku, product.product_name
FROM
product
WHERE
product.id NOT IN (SELECT product_id FROM invoice_item)
GROUP BY
1,2
ORDER BY
product.sku ASC;
4. 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
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 ASC
SELECT invoice.city_name,
product.product_name,
invoice_item.line_total_price
FROM invoice_item as invoice_item
JOIN product as product
ON invoice_item.product_id = product.id
JOIN (
SELECT invoice.id,
customer.city_name
FROM invoice as invoice
JOIN (
SELECT customer.id,city.city_name
FROM customer as customer
LEFT JOIN city as city
ON customer.city_id = city.id
)as customer
ON invoice.customer_id = customer.id
) as invoice
ON invoice_item.invoice_id = invoice.id
ORDER BY invoice_item.line_total_price DESC;
SELECT
CI.city_name,
PR.product_name,
ROUND(SUM(INV_I.line_total_price), 2) AS tot
FROM
city as CI,
customer as CU,
invoice as INV,
invoice_item as INV_I,
product as PR
WHERE
CI.id = CU.city_id
AND CU.id = INV.customer_id
AND INV.id = INV_I.invoice_id
AND INV_I.product_id = PR.id
GROUP BY
CI.city_name,
PR.product_name
ORDER BY
tot DESC,
CI.city_name,
PR.product_name ;
5 Business Expansion
SELECT
contact.user_account_id,
user_account.first_name,
user_account.last_name,
contact.customer_id,
customer.customer_name,
count(*) as numbers
FROM
contact as contact
JOIN
user_account as user_account
ON
contact.user_account_id = user_account.id
JOIN
customer as customer
ON
contact.customer_id = customer.id
WHERE (contact.user_account_id, contact.customer_id) IN (
SELECT user_account_id, customer_id FROM contact GROUP BY user_account_id, customer_id HAVING count(*) > 1
)
GROUP BY
contact.user_account_id ,contact.customer_id, user_account.first_name, user_account.last_name, customer.customer_name;
SELECT
contact.user_account_id,
user_account.first_name,
user_account.last_name,
contact.customer_id,
customer.customer_name,
SUM(CASE WHEN contact.user_account_id IS NOT NULL THEN 1 ELSE 0 END) AS numers
FROM
contact as contact
JOIN
user_account as user_account
ON
contact.user_account_id = user_account.id
JOIN
customer as customer
ON
contact.customer_id = customer.id
WHERE (contact.user_account_id, contact.customer_id) IN (
SELECT user_account_id, customer_id FROM contact GROUP BY user_account_id, customer_id HAVING count(*) > 1
)
GROUP BY
contact.user_account_id ,contact.customer_id, user_account.first_name, user_account.last_name, customer.customer_name;
SELECT
ua.id,
ua.first_name,
ua.last_name,
cu.id AS customer_id,
cu.customer_name,
COUNT(cu.id) AS customer_count
FROM
customer as cu
JOIN
contact as c ON cu.id = c.customer_id
JOIN
user_account as ua ON c.user_account_id = ua.id
GROUP BY
ua.id,
ua.first_name,
ua.last_name,
cu.id,
cu.customer_name
HAVING
COUNT(cu.id) > 1;
6 Invoice Spending
SELECT
cu.customer_name, AVG(i.total_price)
FROM
customer cu
JOIN
invoice i ON cu.id = i.customer_id
GROUP BY
cu.customer_name
HAVING
AVG(i.total_price) <= (SELECT AVG(total_price) FROM invoice) / 4
ORDER BY
AVG(i.total_price) DESC;
The Above Solution Help you to get the Certificate in SQL Intermediate