16. JOIN functions: Intermediate tasks

Intermediate tasks

Task #1: Unsold Book Identification

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}

Click To Show / Hide Solution

-- 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 book
FROM
  `dataacademykz.bookstore.books` b -- Books table alias
JOIN
  `dataacademykz.bookstore.authors` a -- Authors table alias
ON
  b.author_id = a.author_id -- Join condition on author ID
WHERE
  NOT EXISTS (
    SELECT 1 -- Selecting a constant to check existence
    FROM
    `dataacademykz.bookstore.order_details` od -- Order details table
    WHERE
    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.

 

Task #2: Non-Historical Genre Customer Segmentation

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}

Click To Show / Hide Solution

-- Selecting distinct customers who have not purchased books of the 'Historical' genre

SELECT
  DISTINCT c.name, -- Unique customer name
  c.country, -- Customer's country
  c.gender, -- Customer's gender
  c.date_of_birth, -- Customer's date of birth
  EXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM c.date_of_birth) AS age, -- Calculating customer's age
FROM
  `dataacademykz.bookstore.customers` c -- Customers table alias
WHERE
  NOT EXISTS (
    SELECT 1 -- Selecting a constant to check existence
    FROM
    `dataacademykz.bookstore.orders` o -- Orders table
    JOIN
    `dataacademykz.bookstore.order_details` od ON o.order_id = od.order_id -- Joining with order details
    JOIN
    `dataacademykz.bookstore.books` b ON od.book_id = b.book_id -- Joining with books
    WHERE
    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}

Click To Show / Hide Solution

-- Selecting customers with their total revenue, total orders, and ranking by revenue

SELECT
  c.customer_id, -- Customer ID
  c.name, -- Customer name
  ROUND(SUM(o.total_price), 1) AS total_revenue, -- Calculating total revenue per customer
  COUNT(DISTINCT(o.order_id)) AS total_orders, -- Counting total number of orders per customer
  EXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM c.join_date) AS years_as_customer, -- Calculating number of years as a customer
  RANK() OVER (ORDER BY SUM(o.total_price) DESC) AS revenue_rank, -- Ranking customers by their total revenue
FROM
  `dataacademykz.bookstore.customers` c -- Customers table
JOIN
  `dataacademykz.bookstore.orders` o ON c.customer_id = o.customer_id -- Joining with orders table
GROUP BY
  c.name,
  c.customer_id,
  c.join_date -- Grouping to calculate aggregates per customer
ORDER 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.

Data Source: dataacademykz.bookstore.{table_name}