As a bookstore manager, it's important to understand not just what's selling, but also what isn't. Identifying books that haven't been sold can provide insights into consumer preferences and inform inventory management decisions. This task involves analyzing sales data to pinpoint books that have yet to make it into any customer's purchase.
Objective: Identify books that have not been sold, indicating their current lack of popularity or market demand.
Expected Result: A list of unsold books, including details such as the book ID, title, author's name, and genre.
Data Source: dataacademykz.bookstore.{table_name}
-- Selecting books that have not been sold (ordered) SELECT
b.book_id, -- Book ID
b.title, -- Title of the book
a.name AS author_name, -- Author's name
b.genre -- Genre of the bookFROM
`dataacademykz.bookstore.books` b -- Books table aliasJOIN
`dataacademykz.bookstore.authors` a -- Authors table aliasON
b.author_id = a.author_id -- Join condition on author IDWHERENOT EXISTS (
SELECT 1 -- Selecting a constant to check existenceFROM
`dataacademykz.bookstore.order_details` od -- Order details tableWHERE
b.book_id = od.book_id -- Condition to check if the book has been ordered
)
Explanation:
Imagine you're are trying to find out which books have never been checked out. This query is like going through your store's records to find those books.
We look at each book and see if it ever got sold.
If a book hasn’t been sold at all, it makes it to our list with its title and the author’s name.
It’s like making a special shelf of books that are waiting for their first reader.
Technical Breakdown:
This SQL query is designed to identify books that have not been sold. Here's the technical breakdown:
Objective: The goal is to list all books that do not have any corresponding entries in the order_details table, indicating that they have not been sold.
JOIN Operation:
The query uses a JOIN to combine data from the books (aliased as b) and authors (aliased as a) tables.
The join is made on the author_id, which is a common field in both tables, linking each book with its author.
NOT EXISTS Clause:
The NOT EXISTS subquery checks if there are any corresponding entries for each book in the order_details table.
WHERE b.book_id = od.book_id links the books table to order_details. If no match is found, it implies the book has not been sold.
Selection:
The query selects the book ID, title, author name, and genre.
Result:
The output includes a list of books that are currently unsold, along with their titles, authors, and genres.
In this task, we analyze customer purchase histories to segment those who haven't shown an interest in the 'Historical' genre. This segmentation is crucial for understanding diverse customer preferences, which can guide targeted marketing strategies and inventory management decisions. The aim is to recognize customers who are more inclined towards other genres, providing insights for personalized marketing and product offerings.
Objective: Identify customers who have never purchased books from the 'Historical' genre, indicating their preference for other genres.
Expected Result: A list of distinct customers who have not purchased books in the 'Historical' genre, including details like their name, country, gender, date of birth, and calculated age.
Data Source: dataacademykz.bookstore.{table_name}
-- Selecting distinct customers who have not purchased books of the 'Historical' genre SELECTDISTINCT c.name, -- Unique customer name
c.country, -- Customer's country
c.gender, -- Customer's gender
c.date_of_birth, -- Customer's date of birthEXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM c.date_of_birth) AS age, -- Calculating customer's ageFROM
`dataacademykz.bookstore.customers` c -- Customers table aliasWHERENOT EXISTS (
SELECT 1 -- Selecting a constant to check existenceFROM
`dataacademykz.bookstore.orders` o -- Orders tableJOIN
`dataacademykz.bookstore.order_details` od ON o.order_id = od.order_id -- Joining with order detailsJOIN
`dataacademykz.bookstore.books` b ON od.book_id = b.book_id -- Joining with booksWHERE
c.customer_id = o.customer_id AND b.genre = 'Historical' -- Condition to check if the customer has purchased 'Historical' books
)
Explanation:
Imagine you're trying to find out which of your bookstore's customers have never bought a historical book. This query is like scanning through your sales records to spot these customers.
We look through all the purchases and single out customers who haven’t bought any historical books.
For these customers, we note down their name, where they're from, whether they're male or female, and how old they are.
It's like creating a special list of customers who might be interested in other genres but not history books.
Technical Breakdown:
This SQL query aims to identify customers who have not purchased any books in the 'Historical' genre. Here's the technical breakdown:
Objective: The goal is to list customers who have not bought any 'Historical' genre books.
NOT EXISTS Clause:
The query uses a NOT EXISTS subquery to filter out customers who have made purchases in the 'Historical' genre.
The subquery joins orders, order_details, and books tables to check if the customer has bought any books from the 'Historical' genre (WHERE b.genre =
'Historical').
JOIN in Subquery:
The JOIN clauses in the subquery link customers to their orders and the respective books ordered, enabling the check against the 'Historical' genre.
Selection and Distinct:
The main query selects distinct customer names, countries, genders, and dates of birth.
It also calculates each customer's age by subtracting their birth year from the current year.
Result:
The output includes a list of customers who have not purchased books in the 'Historical' genre, along with their demographic information and age.
Task #3: Customer Analytics
Assess and rank your bookstore's customers based on their total spending and loyalty. This task entails compiling a comprehensive profile of each customer, including their total revenue contribution, the number of orders placed, and the duration of their patronage. It's a strategic exercise in customer valuation, creating a 'hall of fame' that highlights your most valuable and committed customers.
Objective: Evaluate and rank your bookstore's customers based on their total spending, loyalty, and the duration of their patronage.
Expected Result: A comprehensive ranking of customers based on their total spending, including details such as total revenue, number of orders, and years as a customer.
Data Source: dataacademykz.bookstore.{table_name}
-- Selecting customers with their total revenue, total orders, and ranking by revenue SELECT
c.customer_id, -- Customer ID
c.name, -- Customer nameROUND(SUM(o.total_price), 1) AS total_revenue, -- Calculating total revenue per customerCOUNT(DISTINCT(o.order_id)) AS total_orders, -- Counting total number of orders per customerEXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM c.join_date) AS years_as_customer, -- Calculating number of years as a customerRANK() OVER (ORDER BYSUM(o.total_price) DESC) AS revenue_rank, -- Ranking customers by their total revenueFROM
`dataacademykz.bookstore.customers` c -- Customers tableJOIN
`dataacademykz.bookstore.orders` o ON c.customer_id = o.customer_id -- Joining with orders tableGROUP BY
c.name,
c.customer_id,
c.join_date -- Grouping to calculate aggregates per customerORDER BY
total_revenue DESC-- Ordering by total revenue in descending order
Explanation:
Imagine you’re running a bookstore and want to find out who your top customers are in terms of how much they've spent.
We add up all the money each customer has spent and count how many orders they’ve made.
We also see how long they've been shopping with us.
Then, we make a leaderboard, ranking them from the biggest spender to the least.
It's like creating a 'hall of fame' for your customers based on how much they've contributed to your store's sales.
Technical Breakdown:
This SQL query is designed to rank customers based on their total revenue contribution. Here's the technical breakdown:
Objective: The goal is to calculate each customer's total revenue contribution, total number of orders, and years as a customer, and then rank them based on total revenue.
Aggregation and Grouping:
The query calculates the total revenue (ROUND(SUM(o.total_price), 1) AS total_revenue) and the total number of distinct orders (COUNT(DISTINCT(o.order_id))
AS
total_orders) for each customer.
Customers are grouped by their ID, name, and join date.
Customer Tenure Calculation:
The query calculates how long each customer has been with the bookstore (EXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR
FROM
c.join_date) AS years_as_customer).
Use of RANK() Window Function:
The RANK() OVER (ORDER BY SUM(o.total_price) DESC) AS
revenue_rank ranks customers based on their total revenue in descending order.
Final Query:
The main query selects customer ID, name, total revenue, total orders, years as a customer, and their revenue rank.
Result:
The output includes a ranked list of customers by total revenue, providing insights into the most valuable customers in terms of revenue contribution.
Task #4: Bookstore Sales Performance and Customer Spending Analysis
In this task, as a bookstore manager, you aim to understand which books are bestsellers and how profitable they are. This involves a deep dive into sales data to reveal crucial insights about customer buying patterns and book profitability.
Objective: Analyze the bookstore's sales performance by identifying best-selling books, their profitability, the number of customers who bought each book, the total revenue generated, and the average spending per customer for each book.
Expected Result: A comprehensive analysis that shows the number of customers per book, total books sold, total revenue, and the average revenue per customer for each book.