170|SQL – Booking Barney’s Bulk Books Buyers

James_Gerstner · April 9, 2024, 2:30am

BYU Student Author: @James_Gerstner
Reviewers: @Jimmy_Han, @Marta_Ellsworth
Estimated Time to Solve: 35 Minutes

We provide the solution to this challenge using:

SQL

Need a program? Click here.

Overview
Barney’s Bulk Books has been around since 1996, when it started selling books with names derived from classic and popular books and magazines (see if you can figure them all out!). Lately, management at Barney’s has wanted to improve data readability, so they’ve turned to you, their accountant, to help with this issue. You have the tables containing all the company’s data, and you’ve decided to use SQL to structure and interpret this data.

Specifically, you want to find:

Which genres are more expensive (to your customers) than others
A ranking of books’ popularity based on how many orders they have
How many sets of Ketchup on a Fry sold in 2023

And, for a bonus from management:
The order of which supplier sells Barney’s the greatest number of sets, and for how much revenue each supplier is ultimately responsible.

Instructions

Download the Microsoft Access database or the Excel document to import the data you will use for this challenge
In Access (or a similar program), navigate to the SQL view by clicking SQL in the bottom right corner or using the dropdown for “View” in the home tab in the top left corner.
Write an SQL Query (Query 1) to display the average price of each genre, ordered low to high. Column names should be: Genre, AvgPrice
Write an SQL Query (Query 2) to display each book that has sold and how many times it has sold, in order of most sales (high to low). Column names should be: ProductName, Genre, SaleCount
Write an SQL Query (Query 3) that will show how many sets of Ketchup on a Fry sold in 2023, as well as the date for each sale. Order the results by SaleDate, with the oldest sale first. Column names should be: ProductID, ProductName, NumSold, SaleDate

Challenge question:
Write an SQL Query (Query 4) that will display each supplier, the number of products they supply to you (including books with no sales), and the total revenue associated with those books. Order the results by the number of products (most to least), then the amount of revenue (most to least). Column names should be: SupplierName, NumProducts, TotalRevenue

Data Files

Challenge170a_Barney’s_Bookstore_Data.accdb
Here’s the same data in an Excel workbook:
Challenge170b_Barney’s_Bookstore_Data.xlsx
Challenge170_Data_Dictionary

Suggestions and Hints

If you get an error that one of the fields in your SELECT statement “isn’t part of an aggregate function,” add the field into the GROUP BY line and see if that helps.
For query 4, nesting queries is probably the best way to solve this; write one query to find the number of products for each supplier, and a query that finds the total revenue per supplier. Then, “nest” one inside of the other using a JOIN.
Solutions for each of the queries can be found in the “Solution” section of this page, separated from one another, so you can check the queries one at a time if you’d like to or if you get stuck.

Solution

Query 1 Solution

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products AS p
GROUP BY Genre
ORDER BY AVG(Price) ASC;

Query 2 Solution

SELECT p.ProductName, p.Genre, COUNT(p.ProductID) AS SaleCount
FROM Products p INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY COUNT(p.ProductID) DESC;

Query 3 Solution

SELECT p.ProductID, p.ProductName, SUM(Quantity) AS NumSold, s.SaleDate
FROM Sales s INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’
AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;

Query 4 Solution

SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Solution Image

Challenge170_Solution_Image

Challenge170_Solution.accdb
Solution Video: Challenge 170|SQL – Booking Barney’s Bulk Books Buyers

Elizabeth_Barton · April 15, 2024, 10:10pm

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products AS p
GROUP BY Genre
ORDER BY AVG(Price) ASC;

SELECT p.ProductName, p.Genre, COUNT(p.ProductID) AS SaleCount
FROM Products p INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY COUNT(p.ProductID) DESC;

SELECT p.ProductID, p.ProductName, SUM(Quantity) AS NumSold, s.SaleDate
FROM Sales s INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’
AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;

SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC

Marc-Antoine · April 27, 2024, 2:45am

1-
SELECT Genre, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Genre
ORDER BY AvgPrice ASC;
2-
SELECT p.ProductName, p.Genre, COUNT(s.ProductID) AS SaleCount
FROM Products p
INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY SaleCount DESC;
3-
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold, s.SaleDate
FROM Sales s
INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’ AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;
4-
SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM Suppliers su
LEFT JOIN Products p ON p.SupplierID = su.SupplierID
LEFT JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

Charles_Smith · June 29, 2024, 4:56am

Alex_Perry · December 10, 2024, 12:35am

20 Minutes
Beginner
Pretty simple SQL queries for someone with experience, great practice though!

Queries:
– 1
SELECT genre, avg(price) as AvgPrice
from Products
group by Genre
order by AvgPrice;

– 2
SELECT ProductName, Genre, count(*) as SaleCount
from Products p
left join Sales s ON p.ProductID = s.ProductID
group by ProductName, Genre
order by SaleCount;

– 3
SELECT p.ProductID, ProductName, count(Quantity) as NumSold, SaleDate
FROM Sales
join products on products.ProductID = sales.ProductID
where ProductName = ‘Ketchup on a Fry’
and year(SaleDate) = 2023
group by ProductID, ProductName, SaleDate
order by SaleDate;

Challenge Problem Solution:
SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM Suppliers su
LEFT JOIN Products p ON p.SupplierID = su.SupplierID
LEFT JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

Gabrielle_Johnson · February 20, 2025, 9:07pm

Time to Complete: 30 minutes
Difficulty: Medium - Hard (The challenge question was hard because we hadn’t learned nested queries yet. But the posted solution helped me figure out how you would do it)
Solution: See Below
1:
SELECT Genre, avg(Price) as AvgPrice
FROM Products
GROUP BY Genre
ORDER BY avg(Price);
2:
SELECT p.ProductName, p.Genre, count(p.ProductID) as SaleCount
FROM Products as p
INNER JOIN Sales as s ON p.productID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY count(p.ProductID) DESC;
3:
SELECT p.ProductID, p.ProductName, sum(s.quantity) AS NumSold, s.SaleDate
FROM Products as p
INNER JOIN Sales as s ON p.ProductID = s.ProductID
WHERE p.ProductID = 111 AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate;
4: Challenge!
SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Miles_Maynes · February 22, 2025, 9:14pm

Time to Complete:25 minutes
Difficulty: Fairly Difficult
Comments: The First 3 Queries were fairly easy, but the challenge Query proved difficult due to use of the Nested Query, and As such I arrived at a Query that provided the number of books sold by each vendor, but it wouldnt sum up the revenue. I ultimately relied on the given solution.

SELECT Genre, AVG(Price) AS AvgPrice
FROM [Products]
GROUP BY Genre
ORDER BY AVG(Price)
SELECT p.ProductName, p.Genre, sum(s.Quantity) AS SaleCount
From [Products] AS p
INNER JOIN [Sales] AS s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.genre
ORDER BY sum(s.Quantity) DESC
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold, s.SaleDate
FROM [Products] AS p
INNER JOIN [Sales] AS s ON p.ProductID = s.ProductID
WHERE p.ProductID = 111 and YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate
SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Henry_Chen · February 24, 2025, 4:54am

time 1hr

SELECT P.Genre, AVG(P.Price) AS AveragePrice
FROM ((Sales AS S INNER JOIN Customers AS C ON C.CustomerID = S.CustomerID) INNER JOIN Products AS P ON P.ProductID = S.ProductID) INNER JOIN Suppliers AS SP ON SP.SupplierID = P.SupplierID
GROUP BY P.Genre;

SELECT P.Genre, P.ProductName, Sum(S.Quantity) AS SalesCount
FROM ((Sales AS S INNER JOIN Customers AS C ON S.CustomerID = C.CustomerID) INNER JOIN Products AS P ON S.ProductID = P.ProductID) INNER JOIN Suppliers AS SP ON P.SupplierID = SP.SupplierID
GROUP BY P.ProductName, P.Genre
ORDER BY Sum(S.Quantity) DESC;

SELECT P.ProductID, P.ProductName, S.SaleDate, SUM(S.Quantity) AS Quantity_Sold
FROM ((Sales AS S INNER JOIN Customers AS C ON S.CustomerID = C.CustomerID) INNER JOIN Products AS P ON S.ProductID = P.ProductID) INNER JOIN Suppliers AS SP ON P.SupplierID = SP.SupplierID
WHERE P.ProductName = “Ketchup on a Fry”
GROUP BY P.ProductID, P.ProductName, S.SaleDate
ORDER BY S.SaleDate DESC;

SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM Suppliers su
LEFT JOIN Products p ON p.SupplierID = su.SupplierID
LEFT JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

Zen_Villondo · February 25, 2025, 8:41pm

Time to Complete: 30 minutes
Difficulty: Medium - Hard
Notes: not familiar with the syntax for the challenge question but the rest was comprehensive

Q1
SELECT p.genre, avg(p.price) as AvgPrice

FROM Products AS p

GROUP BY Genre

ORDER BY avg(p.price)

Q2
SELECT p.ProductName, p.Genre, count(p.ProductID) as SaleCount

FROM Products as p

INNER JOIN Sales as S ON p.ProductID = s.ProductID

GROUP BY p.ProductName, p.Genre

ORDER BY count(p.ProductID) DESC

Q3
SELECT p.ProductID, p.ProductName, SUM(Quantity) AS NumSold, s.SaleDate

FROM Sales s INNER JOIN Products p ON p.ProductID = s.ProductID

WHERE p.ProductName LIKE “Ketchup on a Fry”

AND YEAR(s.SaleDate) = 2023

GROUP BY p.ProductID, p.ProductName, s.SaleDate

ORDER BY s.SaleDate ASC;

Q4
SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue

FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)

LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)

LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts

FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID

GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts

ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Spencer_McArthur · February 25, 2025, 8:42pm

TTC: 15
Ranking: hard
Solutions:

q1
SELECT Genre, avg(price)

FROM Products

Group by Genre

Order by avg(price)

q2
SELECT ProductName, Genre, Sum(quantity) as SaleCount

FROM products as p INNER JOIN sales as s on p.productid = s.productid

GROUP BY ProductName, genre

Order by Sum(quantity) desc;

q3
SELECT p.ProductID, p.ProductName, Sum(quantity) as NumSold, SaleDate

FROM Products as p INNER JOIN Sales as s on p.productid = s.productid

Where ProductName = “Ketchup on a Fry” and year(s.saledate) = 2023

group by p.ProductID, p.ProductName, saledate

ORDER BY SaleDate;
q4
SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue

FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)

LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)

LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts

FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID

GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts

ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Ashleigh_Layton · February 26, 2025, 12:35am

Time to complete: 35 min
Difficulty: Intermediate
Solution:

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Genre
ORDER BY AvgPrice;
SELECT p.ProductName, p.Genre, COUNT(s.ProductID) AS SaleCount
FROM Products AS p
INNER JOIN Sales AS s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY SaleCount DESC;
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold, s.SaleDate
FROM Products AS p
INNER JOIN Sales AS s ON p.ProductID = s.ProductID
WHERE p.ProductID = 111 AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate;
SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price*s.Quantity) AS TotalRevenue
FROM (Suppliers AS su
LEFT JOIN Products AS p ON p.SupplierID = su.SupplierID)
LEFT JOIN Sales AS s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

Charlie_Canady · February 26, 2025, 10:33pm

1:
SELECT Genre, avg(Price) as AvgPrice
FROM Products
GROUP BY Genre
ORDER BY avg(Price);
2:
SELECT p.ProductName, p.Genre, count(p.ProductID) as SaleCount
FROM Products as p
INNER JOIN Sales as s ON p.productID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY count(p.ProductID) DESC;
3:
SELECT p.ProductID, p.ProductName, sum(s.quantity) AS NumSold, s.SaleDate
FROM Products as p
INNER JOIN Sales as s ON p.ProductID = s.ProductID
WHERE p.ProductID = 111 AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate;
4: Challenge!
SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Caden_Vance · February 27, 2025, 5:51am

Time to complete: 50 minutes
Difficulty: Intermediate

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Genre
ORDER BY AVG(Price);
SELECT ProductName, Genre, COUNT(Sales.ProductID) AS SaleCount
FROM Sales INNER JOIN Products ON Sales.ProductID=Products.ProductID
GROUP BY ProductName, Genre
ORDER BY COUNT(Sales.ProductID) DESC;
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold,s.SaleDate
FROM Products AS p INNER JOIN Sales AS s on p.productID=s.productID
WHERE p.ProductID =111 AND YEAR(SaleDate)=2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;
SELECT sup.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(s.Quantityp.Price) AS TotalRevenue
FROM (Suppliers AS sup INNER JOIN Products as p ON sup.SupplierID=p.SupplierID)
LEFT JOIN Sales as s ON p.ProductID=s.ProductID
GROUP BY sup.SupplierName
ORDER BY COUNT(p.ProductID)DESC, SUM(s.Quantityp.Price) DESC;

Colby_Field · February 27, 2025, 8:56am

Time to Complete: 30 minutes
Rating: Intermediate to Difficult
Solutions:

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Genre
ORDER BY AvgPrice ASC;
SELECT p.ProductName, p.Genre, COUNT(s.ProductID) AS SaleCount
FROM Products AS p INNER JOIN Sales AS s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY SaleCount DESC;
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold, s.SaleDate
FROM Products AS p INNER JOIN Sales AS s ON p.ProductID = s.ProductID
WHERE p.ProductID = 111 AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate;
SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price*s.Quantity) AS TotalRevenue
FROM (Suppliers AS su LEFT JOIN Products AS p ON p.SupplierID = su.SupplierID)
LEFT JOIN Sales AS s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

Rylee_Schriever · March 12, 2025, 3:32am

20 mins
intermediate

SELECT p.Genre, AVG(p.price) AS AvgPrice
FROM products p
Group BY p.Genre
ORDER BY AVG(p.price)
SELECT p.ProductName, p.Genre, COUNT(s.Quantity) AS SaleCount
FROM Products p INNER JOIN Sales s ON p.ProductID=s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY COUNT(s.Quantity) DESC;
SELECT p.ProductID, p.ProductName, s.Quantity AS NumSold, s.SaleDate
FROM Products p INNER JOIN Sales s ON p.ProductID=s.ProductID
WHERE p.ProductName = ‘Ketchup on a Fry’ AND YEAR(s.SaleDate)= 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate, s.Quantity
ORDER BY s.SaleDate DESC;

Cragun_Bastian · March 15, 2025, 5:12am

Time to Complete: 90 minutes (with challenge problem)
Intermediate
Challenge question harder than the others, but definitely doable!

1- SELECT Products.Genre, Avg(Products.Price) AS AvgPrice
FROM Products
GROUP BY Products.Genre
ORDER BY Avg(Products.Price);

2-SELECT Products.ProductName, Products.Genre, Count(Sales.ProductID) AS SaleCount
FROM Sales
INNER JOIN Products ON Sales.ProductID=Products.ProductID
GROUP BY Products.ProductName, Products.Genre
ORDER BY Count(Sales.ProductID) DESC;

3-SELECT Sales.ProductID, Products.ProductName, Sales.Quantity AS NumSold, Sales.SaleDate
FROM Sales
INNER JOIN Products ON Sales.ProductID=Products.ProductID
WHERE Year(Sales.SaleDate)=2023
AND Products.ProductName Like “Ketchup on a Fry”
ORDER BY Sales.SaleDate;

4-SELECT SupProdCount.SupName AS SupplierName, SupProdCount.NumProducts, SupTotRev.TotalRevenue

FROM

(SELECT Suppliers.SupplierName AS SupName, Count(Products.ProductID) AS NumProducts
FROM (Suppliers
INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID)
GROUP BY Suppliers.SupplierName
ORDER BY Count(Products.ProductID) DESC) AS SupProdCount

INNER JOIN

(SELECT Suppliers.SupplierName AS SupName,Sum(Products.PriceSales.Quantity) AS TotalRevenue
FROM (Suppliers
INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID)
Left JOIN Sales ON Products.ProductID=Sales.ProductID
GROUP BY Suppliers.SupplierName
ORDER BY Sum(Products.PriceSales.Quantity) DESC) AS SupTotRev

ON SupProdCount.SupName=SupTotRev.SupName

ORDER BY SupProdCount.NumProducts DESC, SupTotRev.TotalRevenue DESC;

Topic		Replies	Views
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	137	March 13, 2025
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	566	February 27, 2025
173\|SQL – Shelter Shenanigans Challenges beginner , managerial , sql , analytics , query-writing	69	332	March 13, 2025
171\|SQL – Shelter Shenanigans Sequel Challenges intermediate , managerial , sql , analytics , query-writing	7	133	February 27, 2025
177\|SQL – Intro to Join Statements Challenges managerial , sql , intro , learningpath	323	637	February 27, 2025

170|SQL – Booking Barney’s Bulk Books Buyers

Related topics