Setting up MySQL in your local machine
You can find detailed instructions on how to setup MySQL workbench on your local machine in the following link - https://ladvien.com/data-analytics-mysql-localhost-setup/
Getting the Goodreads books' data
As of December 8th, 2020, Goodreads no longer issues new developer keys for their public developer API and plans to retire the current version of these tools. Hence it wasn't possible for me to directly use their API to scrape the data. Instead, I got data that was already scraped from Kaggle in the following link - https://www.kaggle.com/datasets/jealousleopard/goodreadsbooks
Since this is just a simple exploratory analysis, we can use the available data to see what we can find. But please note that the actual real-world data might be different from what we see here.
Creating a database and a table to load the Books' data
CREATE DATABASE GOODREADS_BOOKS;
USE GOODREADS_BOOKS;
--Setting the encoding as UTF-8
ALTER DATABASE GOODREADS_BOOKS CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--Creating a table for the Goodreads Books csv file
CREATE TABLE IF NOT EXISTS BOOKS (
BOOK_ID INT,
TITLE VARCHAR(500) NOT NULL,
AUTHORS VARCHAR(500),
AVERAGE_RATING FLOAT,
ISBN INT NOT NULL,
ISBN13 INT NOT NULL,
LANGUAGE_CODE TEXT,
NUM_PAGES INT,
RATINGS_COUNT INT,
TEXT_REVIEWS_COUNT INT,
PUBLICATION_DATE DATE,
PUBLISHER TEXT,
PRIMARY KEY (BOOK_ID)
) ENGINE=MyISAM CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Set OPT_LOCAL_INFILE=1 under Others in the 'Advanced' tab under the 'localhost' connection to enable loading csv files from anywhere in the system.
--Loading the csv file from local into the Books table
LOAD DATA LOCAL INFILE "C:/Users/balaji/books.csv" -- Use the location where the csv file is stored
INTO TABLE BOOKS
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Performing exploratory analysis using the loaded data
Basic checks
High Level Info on the data at hand
--Checking the number of records to see if the data load happened correctly
SELECT COUNT(*) FROM BOOKS;
--Checking the earliest and latest date of book publication
SELECT MIN(PUBLICATION_DATE) AS EARLIEST_DATE,
MAX(PUBLICATION_DATE) AS LATEST_DATE
FROM BOOKS;
--Getting the total number of unique books, authors, publishers, and languages available in the dataset
SELECT COUNT(*) AS TOTAL_RECORDS,
COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS,
COUNT(DISTINCT AUTHORS) AS NUM_AUTHORS,
COUNT(DISTINCT PUBLISHER) AS NUM_PUBLISHERS,
COUNT(DISTINCT LANGUAGE_CODE) AS NUM_LANGUAGES
FROM BOOKS;
--Checking the number of distinct English language codes
SELECT DISTINCT LANGUAGE_CODE FROM BOOKS WHERE LOWER(LANGUAGE_CODE) LIKE '%en%';
Since there are 5 different variants of English, we can create a new Language column where we combine them all into a single value. This would help us to get the overall trends for the books by language.
ALTER TABLE BOOKS ADD LANG TEXT;
UPDATE BOOKS
SET LANG = CASE WHEN LOWER(LANGUAGE_CODE) LIKE '%en%' THEN 'en'
ELSE LANGUAGE_CODE END;
--Performing a QC to see if the update correctly worked
SELECT DISTINCT LANG FROM BOOKS WHERE LOWER(LANGUAGE_CODE) LIKE '%en%';
--Sorting the languages based on the number of books published
WITH TOTAL_BOOKS AS
(
SELECT COUNT(DISTINCT BOOK_ID) AS TOTAL_BOOKS
FROM BOOKS
)
SELECT LANG, COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS,
CONCAT(ROUND(COUNT(DISTINCT BOOK_ID)*100/(SELECT * FROM TOTAL_BOOKS),2),'%') AS PERC_OF_TOTAL_BOOKS
FROM BOOKS
GROUP BY 1
ORDER BY 2 DESC,1;
--Getting the minimum, maximum and average user rating of all the available books
SELECT MIN(AVERAGE_RATING) AS MIN_RATING,
MAX(AVERAGE_RATING) AS MAX_RATING,
ROUND(AVG(AVERAGE_RATING),2) AS AVG_RATING
FROM BOOKS;
--Getting the median user rating of all the available books
SET @row_index := -1;
SELECT ROUND(AVG(subq.AVERAGE_RATING),2) as MEDIAN
FROM
(
SELECT @row_index:=@row_index + 1 AS row_index, AVERAGE_RATING
FROM BOOKS
ORDER BY AVERAGE_RATING
) AS subq
WHERE subq.row_index
IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));
--Getting the average number of user ratings and average number of text reviews available
SELECT ROUND(AVG(RATINGS_COUNT),2) AS AVG_RATINGS_COUNT,
ROUND(AVG(TEXT_REVIEWS_COUNT),2) AS AVG_TEXT_REVIEWS_COUNT
FROM BOOKS;
--Getting the median number of user ratings
SET @row_index := -1;
SELECT ROUND(AVG(subq.RATINGS_COUNT),2) as MEDIAN_RATINGS_COUNT
FROM
(
SELECT @row_index:=@row_index + 1 AS row_index, RATINGS_COUNT
FROM BOOKS
ORDER BY RATINGS_COUNT
) AS subq
WHERE subq.row_index
IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));
----Getting the median number of text reviews
SET @row_index := -1;
SELECT ROUND(AVG(subq.TEXT_REVIEWS_COUNT),2) as MEDIAN_TEXT_REVIEWS_COUNT
FROM
(
SELECT @row_index:=@row_index + 1 AS row_index, TEXT_REVIEWS_COUNT
FROM BOOKS
ORDER BY TEXT_REVIEWS_COUNT
) AS subq
WHERE subq.row_index
IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));
Since the average # of ratings and average # of user text reviews are way higher than the median count, we can clearly say that the data for # ratings and # of user text reviews is positively skewed. Hence in order to objectively look at any insights, it would make sense to go with the median instead of the mean.
Top 15 publishers based on the number of books published
SELECT PUBLISHER, COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 books with the highest number of pages
SELECT TITLE, NUM_PAGES
FROM BOOKS
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 Books with the highest number of ratings
SELECT TITLE, RATINGS_COUNT
FROM BOOKS
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 Books with the highest number of text reviews
SELECT TITLE, TEXT_REVIEWS_COUNT
FROM BOOKS
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 Authors based on the number of books
SELECT AUTHORS, COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 Books with the highest rating (Only considering those books wth atleast median number of ratings and median number of text reviews)
SELECT TITLE, AVERAGE_RATING
FROM BOOKS
WHERE RATINGS_COUNT >= 745 AND TEXT_REVIEWS_COUNT >= 46 --It is advisable to create static tables to store variables in case you use it regularly as it would help you to avoid hardcoding every time
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 Authors with the highest average rating (Only considering those books wth atleast median number of ratings and median number of text reviews)
SELECT AUTHORS, ROUND(AVG(AVERAGE_RATING),2) AS AVG_RATING
FROM BOOKS
WHERE RATINGS_COUNT >= 745 AND TEXT_REVIEWS_COUNT >= 46
GROUP BY 1
ORDER BY 2 DESC, 1
LIMIT 15;
Top 15 highest number of collaborations between Author and Publishing house
SELECT AUTHORS, PUBLISHER, COUNT(DISTINCT TITLE) AS COLLABS
FROM BOOKS
GROUP BY 1,2
ORDER BY 3 DESC, 1 ASC
LIMIT 15;
Deeper exploration
Authors who have published books in more than 1 language
WITH TOTAL_AUTHORS AS
(
SELECT COUNT(DISTINCT AUTHORS) AS TOTAL_AUTHORS
FROM BOOKS
)
SELECT COUNT(DISTINCT AUTHORS) AS NUM_AUTHORS, CONCAT(ROUND(COUNT(DISTINCT AUTHORS)*100/(SELECT * FROM TOTAL_AUTHORS),2),'%') AS PERC_OF_TOTAL_AUTHORS
FROM
(
SELECT AUTHORS, COUNT(DISTINCT LANG) AS NUM_LANG
FROM BOOKS
GROUP BY 1
HAVING NUM_LANG > 1
) A;
Authors with more than 1 book published
WITH TOTAL_AUTHORS AS
(
SELECT COUNT(DISTINCT AUTHORS) AS TOTAL_AUTHORS
FROM BOOKS
)
SELECT COUNT(DISTINCT AUTHORS) AS NUM_AUTHORS, CONCAT(ROUND(COUNT(DISTINCT AUTHORS)*100/(SELECT * FROM TOTAL_AUTHORS),2),'%') AS PERC_OF_TOTAL_AUTHORS
FROM
(
SELECT AUTHORS, COUNT(DISTINCT TITLE) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
HAVING NUM_BOOKS > 1
) A;
Among authors with more than 1 book, top 15 longest time taken to publish between 2 consecutive books
WITH AUTHORS AS
(
SELECT DISTINCT AUTHORS FROM
(
SELECT AUTHORS, COUNT(DISTINCT TITLE) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
HAVING NUM_BOOKS > 1
) A
)
SELECT A.*, ROUND(NUM_DAYS/365.2425, 2) AS NUM_YEARS --mean length of a calendar year in days over a 400 year span (365.2425)
FROM
(
SELECT TITLE, AUTHORS, PUBLICATION_DATE, PREV_PUB_DATE, DATEDIFF(PUBLICATION_DATE, PREV_PUB_DATE) AS NUM_DAYS
FROM
(
SELECT TITLE, AUTHORS, AVERAGE_RATING, NUM_PAGES, RATINGS_COUNT, TEXT_REVIEWS_COUNT, PUBLICATION_DATE,
MAX(PUBLICATION_DATE) OVER (PARTITION BY AUTHORS ORDER BY PUBLICATION_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PREV_PUB_DATE
FROM BOOKS
WHERE AUTHORS IN (SELECT * FROM AUTHORS)
ORDER BY PUBLICATION_DATE
) A
ORDER BY NUM_DAYS DESC
LIMIT 15
) A;
This is the result we are getting with the dataset we have. A complete dataset with accurate publication dates would give us a much clearer picture. We should ideally remove any anniversary editions and re-releases printed after the author's death to avoid huge difference between years. But that is a project for another day!
Among authors with more than 1 book, top 15 biggest increase in average rating between 2 consecutive books (only considering books with atleast median number of ratings and median number of text reviews)
WITH AUTHORS AS
(
SELECT DISTINCT AUTHORS FROM
(
SELECT AUTHORS, COUNT(DISTINCT TITLE) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
HAVING NUM_BOOKS > 1
) A
)
SELECT TITLE, PREV_TITLE, AUTHORS, AVERAGE_RATING, PREV_AVERAGE_RATING, ROUND(AVERAGE_RATING - PREV_AVERAGE_RATING, 2) AS RATING_DIFF
FROM
(
SELECT TITLE, AUTHORS, AVERAGE_RATING, NUM_PAGES, RATINGS_COUNT, TEXT_REVIEWS_COUNT, PUBLICATION_DATE,
ROUND(MAX(AVERAGE_RATING) OVER (PARTITION BY AUTHORS ORDER BY PUBLICATION_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), 2) AS PREV_AVERAGE_RATING,
MAX(TITLE) OVER (PARTITION BY AUTHORS ORDER BY PUBLICATION_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PREV_TITLE
FROM BOOKS
WHERE AUTHORS IN (SELECT * FROM AUTHORS) AND RATINGS_COUNT >= 745 AND TEXT_REVIEWS_COUNT >= 46 --Values found earlier
ORDER BY PUBLICATION_DATE
) A
WHERE PREV_AVERAGE_RATING IS NOT NULL
ORDER BY RATING_DIFF DESC
LIMIT 15;
Among authors with more than 1 book, top 15 biggest decrease in average rating between 2 consecutive books (only considering books with atleast median number of ratings and median number of text reviews)
WITH AUTHORS AS
(
SELECT DISTINCT AUTHORS FROM
(
SELECT AUTHORS, COUNT(DISTINCT TITLE) AS NUM_BOOKS
FROM BOOKS
GROUP BY 1
HAVING NUM_BOOKS > 1
) A
)
SELECT TITLE, PREV_TITLE, AUTHORS, AVERAGE_RATING, PREV_AVERAGE_RATING, ROUND(AVERAGE_RATING - PREV_AVERAGE_RATING, 2) AS RATING_DIFF
FROM
(
SELECT TITLE, AUTHORS, AVERAGE_RATING, NUM_PAGES, RATINGS_COUNT, TEXT_REVIEWS_COUNT, PUBLICATION_DATE,
ROUND(MAX(AVERAGE_RATING) OVER (PARTITION BY AUTHORS ORDER BY PUBLICATION_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), 2) AS PREV_AVERAGE_RATING,
MAX(TITLE) OVER (PARTITION BY AUTHORS ORDER BY PUBLICATION_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PREV_TITLE
FROM BOOKS
WHERE AUTHORS IN (SELECT * FROM AUTHORS) AND RATINGS_COUNT >= 745 AND TEXT_REVIEWS_COUNT >= 46 --Values found earlier
ORDER BY PUBLICATION_DATE
) A
WHERE PREV_AVERAGE_RATING IS NOT NULL
ORDER BY RATING_DIFF ASC
LIMIT 15;
Grouping books by their user rating
WITH TOTAL_BOOKS AS
(
SELECT COUNT(DISTINCT BOOK_ID) AS TOTAL_BOOKS
FROM BOOKS
)
SELECT CASE WHEN AVERAGE_RATING >= 4.5 THEN '4.5+'
WHEN AVERAGE_RATING >= 4.0 AND AVERAGE_RATING < 4.5 THEN '4.0 - 4.5'
WHEN AVERAGE_RATING >= 3.5 AND AVERAGE_RATING < 4.0 THEN '3.5 - 4.0'
WHEN AVERAGE_RATING >= 3.0 AND AVERAGE_RATING < 3.5 THEN '3.0 - 3.5'
WHEN AVERAGE_RATING >= 2.5 AND AVERAGE_RATING < 3.0 THEN '2.5 - 3.0'
WHEN AVERAGE_RATING < 2.5 THEN '0 - 2.5' END AS RATING,
COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS,
CONCAT(ROUND(COUNT(DISTINCT BOOK_ID)*100/(SELECT * FROM TOTAL_BOOKS),2),'%') AS PERC_OF_TOTAL_BOOKS
FROM BOOKS
GROUP BY 1;
Grouping books based on the number of pages
WITH TOTAL_BOOKS AS
(
SELECT COUNT(DISTINCT BOOK_ID) AS TOTAL_BOOKS
FROM BOOKS
)
SELECT CASE WHEN NUM_PAGES >= 0 AND NUM_PAGES <= 100 THEN '0 - 100'
WHEN NUM_PAGES >= 101 AND NUM_PAGES <= 200 THEN '101 - 200'
WHEN NUM_PAGES >= 201 AND NUM_PAGES <= 300 THEN '201 - 300'
WHEN NUM_PAGES >= 301 AND NUM_PAGES <= 400 THEN '301 - 400'
WHEN NUM_PAGES >= 401 AND NUM_PAGES <= 500 THEN '401 - 500'
WHEN NUM_PAGES > 500 THEN '500+' END AS NUM_PAGES,
COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS,
CONCAT(ROUND(COUNT(DISTINCT BOOK_ID)*100/(SELECT * FROM TOTAL_BOOKS),2),'%') AS PERC_OF_TOTAL_BOOKS
FROM BOOKS
GROUP BY 1;
Grouping books based on their year of publication
WITH TOTAL_BOOKS AS
(
SELECT COUNT(DISTINCT BOOK_ID) AS TOTAL_BOOKS
FROM BOOKS
)
SELECT CASE WHEN PUB_YEAR <= 1950 THEN '1900 - 1950'
WHEN PUB_YEAR > 1950 AND PUB_YEAR <= 2000 THEN '1951 - 2000'
ELSE '2001 - PRESENT' END AS FINAL_PUB_YEAR,
COUNT(DISTINCT BOOK_ID) AS NUM_BOOKS,
CONCAT(ROUND(COUNT(DISTINCT BOOK_ID)*100/(SELECT * FROM TOTAL_BOOKS),2),'%') AS PERC_OF_TOTAL_BOOKS
FROM
(
SELECT A.*, YEAR(PUBLICATION_DATE) AS PUB_YEAR
FROM BOOKS A
) A
GROUP BY 1;