SQL Intermediate Certification

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

Start Test

Leave a Reply

Your email address will not be published. Required fields are marked *