/*PROJECT TITLE: Online Library Management System Database Architecture.

Project Description:

The Online Library Management System is a comprehensive project to develop a fully functional library management system using Microsoft SQL Server. This project is designed to gain hands-on experience in database design, data manipulation, querying, and access control. It will encompass various phases and tasks that align with DML, DDL, DQL, and DCL operations.

*/

--Creating the library database
CREATE DATABASE LIBRARY;
USE LIBRARY;

--PROJECT PHASES AND TASKS:
SELECT *
FROM INFORMATION_SCHEMA.TABLES

--PHASE 1: DATABASE DESIGN (DDL)
--Task 1: Define the Database Schema
--Description: Create the database schema for the library management system, defining tables and relationships.

-- Create 'Books' Table
CREATE TABLE Books (
BookID INT IDENTITY(1,1), --auto increment(1,1)The 'ID' column is automatically populated.
BookTitle VARCHAR(255) NOT NULL,
BookAuthor VARCHAR(100) NOT NULL,
ISBN VARCHAR(100) NOT NULL,--ISBN stands for "International Standard Book Number"
PublishedYear Date,
PubLisher VARCHAR(50) NOT NULL,
Language VARCHAR(50),
Pages INT,
GenreID INT,
PRIMARY KEY (BookID)
);

-- Creating the 'Genres' Table
CREATE TABLE Genres (
GenreID INT IDENTITY(1,1) NOT NULL,
Genre VARCHAR(50),
PRIMARY KEY (GenreID)
);

-- Creating the 'Transactions' Table
CREATE TABLE Transactions (
BookID INT NOT NULL,
UserID INT NOT NULL,
CheckoutDate DATE NOT NULL,
ReturnDate DATE NOT NULL
);

-- Creating the 'Users' Table
CREATE TABLE Users (
UserID INT IDENTITY (1,1) NOT NULL,--auto increment(1,1)The 'ID' column is automatically populated.
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) NOT NULL,
Gender VARCHAR(50) NOT NULL,
DOB DATE NOT NULL,
PhoneNumber NVARCHAR(20) NOT NULL,
PRIMARY KEY (UserID)
);

--Task 2: Specify Keys and Constraints
--Description: Define primary keys, foreign keys, constraints, and relationships between tables.

/* ALTER TABLE DICTIONARY(EXAMPLES)
--ADDING A NEW COLUMN:
--ALTER TABLE Employee
--ADD BirthDate DATE;
--MODIFYING A COLUMN'S DATA TYPE:
--ALTER TABLE Employee
--ALTER COLUMN Salary DECIMAL(10,2);
--DROPPING A COLUMN:
--ALTER TABLE Employee
--DROP COLUMN BirthDate;
--ADDING A PRIMARY KEY:
--ALTER TABLE Employee
--ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);
--ADDING A FOREIGN KEY:
--ALTER TABLE Orders
--ADD CONSTRAINT FK_CustomerID
--FOREIGN KEY (CustomerID)
--REFERENCES Customers(CustomerID);
*/

--Defining foreign key for GenreID in Books Table
ALTER TABLE Books
ADD CONSTRAINT FK_Books_Genres FOREIGN KEY (GenreID)
REFERENCES Genres(GenreID);

--Defining foreign key for BookID in Transactions Table
ALTER TABLE Transactions
ADD CONSTRAINT FK_Transactions_Books FOREIGN KEY (BookID)
REFERENCES Books(BookID);

ALTER TABLE Transactions
ADD CONSTRAINT FK_Transactions_Users FOREIGN KEY (UserID)
REFERENCES Users(UserID);

--PHASE 2: DATA POPULATION (DML)
--Task 3: Populate the Database with Sample Data
--Description: Insert sample data into the database, including books, genres, roles, transactions and users.

--Inserting Sample of Books Records
INSERT INTO Books
(BookTitle, BookAuthor, ISBN, PublishedYear, Publisher, Language, Pages, GenreID)
VALUES
('The Secret of Shadows', 'Emily Johnson', '978-1-123456-78-9', 2010, 'Fictional Publishing', 'English', 320, 15),
('Whispers in the Wind', 'Michael Davis', '978-2-234567-89-0', 2015, 'Dreamscape Books', 'English', 420, 28),
('Chronicles of Elara', 'Sophie Roberts', '978-3-345678-90-1', 2018, 'Enchanted Press', 'English', 280, 14),
('Midnight Serenade', 'Alex Turner', '978-4-456789-01-2', 2012, 'Moonlit Publications', 'English', 360, 47),
('Ephemeral Echoes', 'Natalie Reed', '978-5-567890-12-3', 2019, 'Whimsical Books', 'English', 400, 8),
('The Lost City', 'James Patterson', '978-1-123456-78-9', 2015, 'Thriller Publications', 'English', 400, 34),
('Eternal Flames', 'Jennifer Smith', '978-2-234567-89-0', 2018, 'Romance Novels Inc.', 'English', 320, 37),
('Quantum Dreams', 'David Mitchell', '978-3-345678-90-1', 2016, 'Futuristic Books Ltd.', 'English', 450, 41),
('Shadows of the Past', 'Sophie Turner', '978-4-456789-01-2', 2013, 'Mystery Press', 'English', 380, 31),
('Echoes from Elysium', 'Gabriel Garcia', '978-5-567890-12-3', 2017, 'Magical Realism House', 'English', 360, 44),
('Whispering Winds', 'Isabel Allende', '978-6-678901-23-4', 2014, 'Latin Tales Publishing', 'Spanish', 420, 28),
('The Cosmic Odyssey', 'Arthur C. Clarke', '978-7-789012-34-5', 2001, 'Astro Books', 'English', 500, 12),
('A Tale of Two Realms', 'Charles Dickens', '978-8-890123-45-6', 1859, 'Classic Publishing', 'English', 550, 36),
('Beyond the Horizon', 'Agatha Christie', '978-9-901234-56-7', 1920, 'Cozy Mysteries Ltd.', 'English', 320, 30),
('Enigma Code', 'Dan Brown', '978-1-109876-54-3', 2009, 'Conspiracy Books', 'English', 380, 34),
('The Silent Observer', 'Anna Mitchell', '978-1-123456-78-9', 2017, 'Mystery House', 'English', 320, 31),
('Echoes of Eternity', 'Michael Roberts', '978-2-234567-89-0', 2019, 'Eternal Publications', 'English', 420, 14),
('Whispers in the Dark', 'Emily Turner', '978-3-345678-90-1', 2015, 'Dark Mysteries Inc.', 'English', 300, 47),
('The Quantum Enigma', 'Sophie Davis', '978-4-456789-01-2', 2016, 'Quantum Books', 'English', 360, 41),
('Lost in Translation', 'Carlos Garcia', '978-5-567890-12-3', 2018, 'Babel Press', 'Spanish', 280, 28),
('Midnight Serenade', 'Isabel Smith', '978-6-678901-23-4', 2014, 'Moonlit Publications', 'English', 380, 37),
('The Enchanted Forest', 'David Clarke', '978-7-789012-34-5', 2013, 'Enchanted Tales', 'English', 440, 12),
('A Tale of Shadows', 'Charlotte Dickens', '978-8-890123-45-6', 1875, 'Classic Novels Ltd.', 'English', 500, 36),
('City of Whispers', 'Agatha Turner', '978-9-901234-56-7', 2020, 'Urban Stories Publishing', 'English', 340, 48),
('The Cosmic Voyage', 'Arthur Christie', '978-1-109876-54-3', 2005, 'Astro Books', 'English', 420, 12),
('The Secret Garden', 'Frances Hodgson Burnett', '978-0-123456-78-9', 1911, 'Classic Novels Ltd.', 'English', 320, 36),
('The Great Gatsby', 'F. Scott Fitzgerald', '978-0-234567-89-0', 1925, 'Jazz Age Press', 'English', 180, 37),
('Brave New World', 'Aldous Huxley', '978-0-345678-90-1', 1932, 'Dystopian Books', 'English', 280, 41),
('To Kill a Mockingbird', 'Harper Lee', '978-0-456789-01-2', 1960, 'Southern Classics', 'English', 320, 30),
('1984', 'George Orwell', '978-0-567890-12-3', 1949, 'Totalitarian Publishing', 'English', 340, 41),
('The Hobbit', 'J.R.R. Tolkien', '978-0-678901-23-4', 1937, 'Fantasy Realms', 'English', 320, 13),
('Pride and Prejudice', 'Jane Austen', '978-0-789012-34-5', 1813, 'Regency Novels', 'English', 400, 36),
('One Hundred Years of Solitude', 'Gabriel Garcia Marquez', '978-0-890123-45-6', 1967, 'Magical Realism House', 'Spanish', 420, 29),
('The Catcher in the Rye', 'J.D. Salinger', '978-0-901234-56-7', 1951, 'Rebel Writers', 'English', 240, 48),
('The Shadow Conspiracy', 'Lucy Anderson', '978-1-123456-78-9', 2016, 'Conspiracy House', 'English', 380, 34),
('Eternal Love', 'John Reynolds', '978-2-234567-89-0', 2019, 'Romantic Novels Inc.', 'English', 300, 37),
('Quantum Entanglement', 'Olivia Thompson', '978-3-345678-90-1', 2017, 'Quantum Books', 'English', 420, 41),
('Cryptic Clues', 'Robert Turner', '978-4-456789-01-2', 2015, 'Enigma Press', 'English', 340, 30),
('Lost Legends', 'Maria Garcia', '978-5-567890-12-3', 2018, 'Mythical Tales Publishing', 'English', 400, 32),
('Midnight Whispers', 'Carlos Rodriguez', '978-6-678901-23-4', 2014, 'Moonlit Publications', 'Spanish', 360, 47),
('Enchanted Chronicles', 'Isabella Clarke', '978-7-890123-45-6', 2016, 'Fantasy Realms', 'English', 420, 13),
('Shadows of History', 'Charles Dickens Jr.', '978-8-901234-56-7', 1885, 'Classic Novels Ltd.', 'English', 480, 36),
('Whispers in the City', 'Agatha Thompson', '978-9-012345-67-8', 2021, 'Urban Stories Publishing', 'English', 340, 48),
('The Quantum Odyssey', 'Arthur Christie', '978-1-098765-43-2', 2012, 'Astro Books', 'English', 400, 12),
('The Silent Garden', 'Frances Hodgson Burnett', '978-2-109876-54-3', 1911, 'Classic Novels Ltd.', 'English', 320, 36),
('The Midnight Express', 'Emily Bronte', '978-3-123456-78-9', 1847, 'Gothic Tales Publishing', 'English', 300, 24),
('Journey to the Stars', 'Neil Armstrong', '978-4-234567-89-0', 1969, 'Space Exploration Press', 'English', 240, 42),
('Legends of the Lost City', 'Isaac Asimov', '978-5-345678-90-1', 1950, 'Sci-Fi Classics', 'English', 320, 42),
('Whispers from the Past', 'Charlotte Dickens', '978-6-456789-01-2', 1870, 'Timeless Novels', 'English', 380, 36),
('The Adventure Begins', 'John Author', '1234567890', 2020, 'Adventure Publications', 'English', 300, 3),
('Detective Chronicles', 'Jane Writer', '9876543210', 2018, 'Mystery House', 'English', 250, 9),
('Fantasy Quest', 'Sam Novelist', '4567890123', 2022, 'Imagination Press', 'English', 400, 14),
('Historical Journeys', 'Alex Historian', '7890123456', 2015, 'Past Publishers', 'English', 350, 20),
('Sci-Fi Odyssey', 'Eva Futurist', '2345678901', 2019, 'Future Books Inc.', 'English', 320, 38),
('The Mysterious Case', 'Chris Sleuth', '1112223334', 2017, 'Enigma Press', 'English', 280, 9),
('Romantic Escapade', 'Lisa Lovestory', '5556667778', 2021, 'Heartfelt Publications', 'English', 220, 36),
('Humor Unleashed', 'Tom Jester', '9998887776', 2016, 'Laugh Out Loud Books', 'English', 180, 25),
('Historical Reflections', 'Victoria Past', '4443332220', 2014, 'Timeless Editions', 'English', 310, 20),
('Epic Fantasy Saga', 'Michael Epic', '1230987654', 2023, 'Epic Adventures Ltd.', 'English', 500, 14),
('Science and Society', 'Alan Scientist', '7654321098', 2020, 'Discovery Press', 'English', 280, 37),
('A Poets Dream', 'Emily Verses', '3456789012', 2019, 'Rhyme and Reason Publishers', 'English', 150, 31),
('Love in the City', 'Carlos Romance', '7890123456', 2018, 'Urban Love Books', 'English', 240, 43),
('Theatre of Imagination', 'Olivia Playwright', '2345678901', 2015, 'Dramatic Works Inc.', 'English', 190, 45),
('Western Frontier', 'Billy Wrangler', '8765432109', 2016, 'Frontier Press', 'English', 260, 46),
('Mythical Tales', 'Diana Mythmaker', '1357902468', 2017, 'Mythical Publishing', 'English', 330, 30),
('Essays on Life', 'Frank Ponderer', '8642097531', 2022, 'Contemplation Books', 'English', 210, 11),
('The Satirical Mind', 'Gary Satirist', '2468013579', 2020, 'Laughing Stock Publishers', 'English', 200, 35),
('Social Science Chronicles', 'Samantha Scholar', '9876543210', 2016, 'Insightful Press', 'English', 280, 44),
('Fables of Wisdom', 'Wendy Fabulist', '1029384756', 2015, 'Moral Tales Inc.', 'English', 170, 12),
('The Urban Jungle', 'Leo Cityscape', '5678901234', 2018, 'Metropolis Books', 'English', 230, 42),
('Dramatic Dialogues', 'Peter Playwright', '3141592653', 2021, 'Theatrical Works', 'English', 260, 45),
('Tall Tales and Legends', 'Larry Storyteller', '2718281829', 2019, 'Legendary Publishing', 'English', 300, 32),
('Mystery at Midnight', 'Nancy Sleuth', '9876543210', 2017, 'Enigma Press', 'English', 240, 9),
('Whimsical Wonderland', 'Alice Dreamer', '1234567890', 2020, 'Imaginary Books', 'English', 180, 15),
('Historical Whirlwind', 'Henry Historybuff', '4567890123', 2014, 'Time Travel Press', 'English', 320, 21),
('Action-packed Thriller', 'Victor Thrillseeker', '9876543210', 2016, 'Adrenaline Publishers', 'English', 270, 1),
('Magical Realism Revelations', 'Rita Realist', '2345678901', 2018, 'Enchanted Editions', 'English', 250, 29),
('Epic Poetry Anthology', 'Penny Poet', '8765432109', 2022, 'Verse Ventures', 'English', 190, 33),
('Comedic Chronicles', 'Charlie Comedian', '1357902468', 2015, 'Laugh Riot Books', 'English', 220, 6),
('The Quantum Paradox', 'Alex Physicist', '9988776655', 2021, 'Quantum Press', 'English', 350, 37),
('Dreams of Tomorrow', 'Elena Visionary', '1122334455', 2023, 'Futuristic Books', 'English', 280, 22),
('Legends of the Wild West', 'Sarah Outlaw', '6677889900', 2015, 'Frontier Tales', 'English', 310, 47),
('City Lights', 'Michael Urbanite', '5544332211', 2019, 'Metropolitan Books', 'English', 240, 43),
('Whodunit Chronicles', 'David Detective', '2233445566', 2018, 'Mystery Masterminds', 'English', 260, 9),
('The Enchanted Garden', 'Alice Bloom', '1122334455', 2020, 'Magical Gardens Press', 'English', 280, 15),
('Historical Intrigues', 'Sophie Historian', '1122334455', 2015, 'Curious Histories', 'English', 280, 20),
('Virtual Realities', 'Zara VR', '6677889900', 2022, 'Digital Dreams Publishing', 'English', 350, 38),
('Jazz and Poetry', 'Miles Jazzman', '5544332211', 2018, 'Harmony Publications', 'English', 200, 31),
('Cityscape Serenade', 'Nina Urbanite', '2233445566', 2020, 'Metropolitan Melodies', 'English', 260, 43),
('Midnight Noir', 'Ava Detective', '9900112233', 2016, 'Noir Mysteries', 'English', 240, 9),
('Whimsical Worlds', 'Oscar Dreamer', '1122334455', 2019, 'Fantastic Fables', 'English', 180, 15),
('Political Commentary', 'Harriet Pundit', '6677889900', 2017, 'Insightful Ink', 'English', 250, 34),
('Exploring the Cosmos', 'Neil Astronomer', '5544332211', 2023, 'Galactic Explorations', 'English', 320, 37),
('Love Beyond Time', 'Eleanor Romance', '2233445566', 2018, 'Timeless Love Stories', 'English', 270, 36),
('Modern Mythologies', 'Myra Mythmaker', '9900112233', 2020, 'Mythical Moderns', 'English', 310, 30),
('The Quantum Code', 'Alan Physicist', '1122334455', 2019, 'Quantum Publications', 'English', 290, 37),
('Dreams of Eternity', 'Isabella Visionary', '6677889900', 2022, 'Eternal Books', 'English', 310, 22),
('Satirical Sketches', 'Simon Satirist', '5544332211', 2021, 'Laugh Lines Press', 'English', 230, 34),
('Interstellar Explorations', 'Ava Explorer', '2233445566', 2016, 'Cosmic Adventures', 'English', 380, 38),
('Love Under the Moonlight', 'Oliver Romantic', '9900112233', 2018, 'Moonlit Romance', 'English', 240, 36),
('Epic Historical Tales', 'Isaac Chronicler', '1122334455', 2017, 'Timeless Chronicles', 'English', 320, 20),
('Future Horizons', 'Nina Futurist', '6677889900', 2020, 'Future Visionaries', 'English', 270, 38),
('Reflections in Rhyme', 'Sam Poet', '5544332211', 2019, 'Reflective Verses', 'English', 190, 31),
('City of Dreams', 'Gabriel Dreamweaver', '2233445566', 2015, 'Dreamscape Publications', 'English', 280, 43),
('Cryptic Chronicles', 'Victoria Sleuth', '9900112233', 2023, 'Mystery Crypt Publications', 'English', 250, 9),
('Magical Creatures Guide', 'Luna Lorekeeper', '1122334455', 2022, 'Enchanted Guides Ltd.', 'English', 260, 14),
('Technological Tales', 'Xander Techie', '6677889900', 2017, 'Tech Trends Publishing', 'English', 330, 37),
('Rhymes of the Rainforest', 'Rosa Rainforest', '5544332211', 2018, 'Nature Nurtures Press', 'English', 200, 12),
('Metropolitan Mysteries', 'Mia Detective', '2233445566', 2021, 'Urban Investigations', 'English', 280, 43),
('Echoes of Eternity', 'Elijah Eternal', '9900112233', 2019, 'Eternal Echoes Books', 'English', 310, 22),
('Historical Echoes II', 'Alice Historian', '1122334455', 2016, 'Echoes of the Past Publishing', 'English', 340, 20),
('Future Realms', 'Zara Futurist', '6677889900', 2023, 'Futuristic Visions', 'English', 300, 38),
('Musings in Motion', 'Mila Mover', '5544332211', 2018, 'Dynamic Publications', 'English', 240, 11),
('Cityscape Symphony', 'Leo Urbanite', '2233445566', 2020, 'Metropolitan Melodies', 'English', 270, 43),
('Nocturnal Noire', 'Nolan Detective', '9900112233', 2017, 'Nocturnal Mysteries', 'English', 250, 9),
('Whimsical Wanderings', 'Wendy Dreamer', '1122334455', 2020, 'Wanderlust Publications', 'English', 180, 15),
('Political Satire II', 'Olivia Satirist', '6677889900', 2019, 'Satirical Express', 'English', 220, 34),
('Galactic Chronicles', 'Gavin Galaxian', '5544332211', 2022, 'Interstellar Stories', 'English', 320, 37),
('Love Across Time', 'Ethan Romance', '2233445566', 2019, 'Timeless Love Stories', 'English', 260, 36),
('Modern Myths Unveiled', 'Mia Mythmaker', '9900112233', 2021, 'Contemporary Myths', 'English', 310, 30),
('Enchanted Realms', 'Elena Enchanter', '1122334455', 2020, 'Realm Explorations', 'English', 290, 14),
('Digital Dimensions', 'Dylan Digit', '6677889900', 2018, 'Digital Publishing House', 'English', 300, 37),
('Verses of the Volcano', 'Vera Volcanic', '5544332211', 2019, 'Erupting Verse Publications', 'English', 210, 12),
('Metropolitan Melancholy', 'Melissa Urbanite', '2233445566', 2017, 'City Sorrow Press', 'English', 260, 43),
('Eternal Embrace', 'Evan Eternal', '9900112233', 2021, 'Endless Love Books', 'English', 320, 22),
('Historical Echoes III', 'Arthur Historian', '1122334455', 2016, 'Echoes of Yesteryear Publishing', 'English', 360, 20),
('Future Frontiers', 'Zoe Futurist', '6677889900', 2023, 'Visionary Ventures', 'English', 280, 38),
('Journey of Juxtaposition', 'Jasmine Juxtaposer', '5544332211', 2018, 'Contrasting Chronicles', 'English', 230, 11),
('Urban Utopia', 'Oscar Urbanite', '2233445566', 2020, 'City Dreams Publications', 'English', 290, 43),
('Midnight Mysteries', 'Mila Mystery', '9900112233', 2016, 'Moonlit Investigations', 'English', 230, 9),
('Whispers of the Wilderness', 'Walter Wild', '1122334455', 2019, 'Wilderness Wonders Publishing', 'English', 250, 20),
('Political Satire III', 'Oliver Satirist', '6677889900', 2020, 'Satirical Insights', 'English', 240, 34),
('Cosmic Chronicles', 'Cleo Cosmonaut', '5544332211', 2022, 'Cosmic Tales', 'English', 330, 37),
('Everlasting Love', 'Eva Evergreen', '2233445566', 2017, 'Eternal Love Stories', 'English', 270, 36),
('Modern Mythologies II', 'Max Mythmaker', '9900112233', 2022, 'Mythical Moderns', 'English', 280, 30);

--Inserting Sample of Genres Records
INSERT INTO Genres
(GenreID, Genre)
VALUES
(1, 'Action'),
(2, 'Action fiction'),
(3, 'Adventure'),
(4, 'Adventure fiction'),
(5, 'Autobiography'),
(6, 'Comedy'),
(7, 'Coming-of-age story'),
(8, 'Crime fiction'),
(9, 'Creative nonfiction'),
(10, 'Detective fiction'),
(11, 'Drama'),
(12, 'Essay'),
(13, 'Fable'),
(14, 'Fairy tale'),
(15, 'Fantasy'),
(16, 'Fantasy Fiction'),
(17, 'Film criticism'),
(18, 'Folklore'),
(19, 'Genre fiction'),
(20, 'Historical fantasy'),
(21, 'Historical Fiction'),
(22, 'History'),
(23, 'Horror'),
(24, 'Humor'),
(25, 'Isekai'),
(26, 'Legend'),
(27, 'Literary fiction'),
(28, 'Literary realism'),
(29, 'Magical Realism'),
(30, 'Memoir'),
(31, 'Mystery'),
(32, 'Mythology'),
(33, 'Narrative'),
(34, 'Non-fiction'),
(35, 'Novel'),
(36, 'Poetry'),
(37, 'Romance'),
(38, 'Romance novel'),
(39, 'Satire'),
(40, 'Science fiction'),
(41, 'Short story'),
(42, 'Social science fiction'),
(43, 'Speech'),
(44, 'Speculative fiction'),
(45, 'Tall tale'),
(46, 'Theatre-fiction'),
(47, 'Thriller'),
(48, 'Urban fiction'),
(49, 'Western fiction'),
(50, 'Western film');

--Inserting Sample of Users Records
INSERT INTO Users
(FirstName, LastName, Email, Gender, DOB, PhoneNumber)
VALUES
('Jane', 'Smith', 'jane.smith@gmail.com', 'Female', '1985-05-22', '987-654-3210'),
('Bob', 'Johnson', 'bob.johnson@gmail.com', 'Male', '1982-11-10', '456-789-0123'),
('Alice', 'Williams', 'alice.williams@gmail.com', 'Female', '1995-07-03', '321-654-0987'),
('Charlie', 'Brown', 'charlie.brown@gmail.com', 'Male', '1988-09-28', '789-012-3456'),
('Eva', 'Miller', 'eva.miller@gmail.com', 'Female', '1993-03-12', '567-890-1234'),
('John', 'Doe', 'john.doe2@gmail.com', 'Male', '1992-04-18', '234-567-8901'),
('Jane', 'Smith', 'jane.smith2@gmail.com', 'Female', '1987-08-05', '890-123-4567'),
('Bob', 'Johnson', 'bob.johnson3@gmail.com', 'Male', '1979-12-01', '345-678-9012'),
('Grace', 'Taylor', 'grace.taylor@gmail.com', 'Female', '1993-09-05', '567-890-1234'),
('Daniel', 'Clark', 'daniel.clark@gmail.com', 'Male', '1980-12-12', '123-456-7890'),
('Sophie', 'Turner', 'sophie.turner@gmail.com', 'Female', '1990-04-19', '890-123-4567'),
('William', 'Baker', 'william.baker@gmail.com', 'Male', '1978-07-25', '234-567-8901'),
('Lily', 'Evans', 'lily.evans@gmail.com', 'Female', '1987-02-28', '456-789-0123'),
('Michael', 'Roberts', 'michael.roberts@gmail.com', 'Male', '1984-05-07', '678-901-2345'),
('Olivia', 'Fisher', 'olivia.fisher@gmail.com', 'Female', '1995-10-14', '901-234-5678'),
('Ethan', 'Woods', 'ethan.woods@gmail.com', 'Male', '1992-01-30', '345-678-9012'),
('Ava', 'Reynolds', 'ava.reynolds@gmail.com', 'Female', '1981-04-15', '567-890-1234'),
('Jacob', 'Chapman', 'jacob.chapman@gmail.com', 'Male', '1989-09-21', '789-012-3456'),
('Emily', 'Morgan', 'emily.morgan@gmail.com', 'Female', '1994-02-26', '234-567-8901'),
('Ryan', 'Gibson', 'ryan.gibson@gmail.com', 'Male', '1989-06-11', '890-123-4567'),
('Sophia', 'Barnes', 'sophia.barnes@gmail.com', 'Female', '1992-10-18', '123-456-7890'),
('Matthew', 'Griffin', 'matthew.griffin@gmail.com', 'Male', '1994-01-01', '456-789-0123'),
('Chloe', 'Ford', 'chloe.ford@gmail.com', 'Female', '1988-04-15', '678-901-2345'),
('Nathan', 'Fletcher', 'nathan.fletcher@gmail.com', 'Male', '1983-08-07', '789-012-3456'),
('Isabella', 'Harrison', 'isabella.harrison@gmail.com', 'Female', '1986-11-24', '901-234-5678'),
('Mason', 'Owens', 'mason.owens@gmail.com', 'Male', '1991-03-12', '234-567-8901'),
('Ella', 'Wallace', 'ella.wallace@gmail.com', 'Female', '1980-05-30', '567-890-1234'),
('Logan', 'Stewart', 'logan.stewart@gmail.com', 'Male', '1996-02-18', '123-456-7890'),
('Aria', 'Spencer', 'aria.spencer@gmail.com', 'Female', '1985-09-15', '890-123-4567'),
('Henry', 'Ferguson', 'henry.ferguson@gmail.com', 'Male', '1993-07-08', '345-678-9012'),
('Victoria', 'Dixon', 'victoria.dixon@gmail.com', 'Female', '1992-12-03', '567-890-1234'),
('Elijah', 'Lawson', 'elijah.lawson@gmail.com', 'Male', '1987-04-26', '789-012-3456'),
('Scarlett', 'Potter', 'scarlett.potter@gmail.com', 'Female', '1984-01-11', '901-234-5678'),
('Liam', 'Carter', 'liam.carter@gmail.com', 'Male', '1988-06-17', '234-567-8901'),
('Ava', 'Perry', 'ava.perry@gmail.com', 'Female', '1990-09-02', '567-890-1234'),
('Jackson', 'Cooper', 'jackson.cooper@gmail.com', 'Male', '1984-03-25', '123-456-7890'),
('Aria', 'Fleming', 'aria.fleming@gmail.com', 'Female', '1989-12-08', '890-123-4567'),
('Oliver', 'Webster', 'oliver.webster@gmail.com', 'Male', '1995-01-31', '456-789-0123'),
('Scarlett', 'Quinn', 'scarlett.quinn@gmail.com', 'Female', '1997-08-14', '789-012-3456'),
('Mason', 'Flynn', 'mason.flynn@gmail.com', 'Male', '1986-05-07', '901-234-5678'),
('Chloe', 'Frost', 'chloe.frost@gmail.com', 'Female', '1994-11-20', '234-567-8901'),
('Ethan', 'Summers', 'ethan.summers@gmail.com', 'Male', '1982-07-03', '567-890-1234'),
('Zoe', 'Bennett', 'zoe.bennett@gmail.com', 'Female', '1983-02-16', '123-456-7890'),
('Caleb', 'Porter', 'caleb.porter@gmail.com', 'Male', '1993-04-12', '890-123-4567'),
('Hazel', 'Woods', 'hazel.woods@gmail.com', 'Female', '1987-10-27', '234-567-8901'),
('Leo', 'Harrison', 'leo.harrison@gmail.com', 'Male', '1981-08-03', '567-890-1234'),
('Luna', 'Bishop', 'luna.bishop@gmail.com', 'Female', '1996-02-18', '123-456-7890'),
('Sebastian', 'Gibson', 'sebastian.gibson@gmail.com', 'Male', '1999-05-29', '456-789-0123'),
('Ivy', 'Fisher', 'ivy.fisher@gmail.com', 'Female', '1984-11-10', '789-012-3456'),
('Owen', 'Wells', 'owen.wells@gmail.com', 'Male', '1992-07-23', '901-234-5678'),
('Avery', 'Chandler', 'avery.chandler@gmail.com', 'Female', '1988-03-06', '234-567-8901'),
('Elijah', 'Sullivan', 'elijah.sullivan@gmail.com', 'Male', '1985-12-19', '567-890-1234'),
('Stella', 'Barker', 'stella.barker@gmail.com', 'Female', '1991-09-02', '123-456-7890'),
('Oliver', 'Fletcher', 'oliver.fletcher@gmail.com', 'Male', '1998-01-14', '345-678-9012'),
('Sophie', 'Greene', 'sophie.greene@gmail.com', 'Female', '1989-06-26', '678-901-2345'),
('Mason', 'Hartman', 'mason.hartman@gmail.com', 'Male', '1995-03-08', '890-123-4567'),
('Emma', 'Ingram', 'emma.ingram@gmail.com', 'Female', '1982-12-21', '234-567-8901'),
('Logan', 'Jennings', 'logan.jennings@gmail.com', 'Male', '1986-07-04', '567-890-1234'),
('Aria', 'Keller', 'aria.keller@gmail.com', 'Female', '1997-11-16', '123-456-7890'),
('Jackson', 'Lambert', 'jackson.lambert@gmail.com', 'Male', '1983-04-29', '456-789-0123'),
('Mia', 'Mackenzie', 'mia.mackenzie@gmail.com', 'Female', '1990-10-12', '789-012-3456'),
('Carter', 'Newton', 'carter.newton@gmail.com', 'Male', '1994-05-24', '901-234-5678'),
('Layla', 'Owens', 'layla.owens@gmail.com', 'Female', '1980-09-07', '234-567-8901'),
('Ethan', 'Pierce', 'ethan.pierce@gmail.com', 'Male', '1984-02-20', '567-890-1234'),
('Hannah', 'Quinn', 'hannah.quinn@gmail.com', 'Female', '1991-12-03', '123-456-7890'),
('Gabriel', 'Reed', 'gabriel.reed@gmail.com', 'Male', '1998-06-15', '456-789-0123'),
('Victoria', 'Santos', 'victoria.santos@gmail.com', 'Female', '1987-01-28', '789-012-3456'),
('Henry', 'Thompson', 'henry.thompson@gmail.com', 'Male', '1996-08-09', '901-234-5678'),
('Isabella', 'Underwood', 'isabella.underwood@gmail.com', 'Female', '1985-03-22', '234-567-8901'),
('Jaxon', 'Vaughn', 'jaxon.vaughn@gmail.com', 'Male', '1992-10-04', '567-890-1234'),
('Kylie', 'Winters', 'kylie.winters@gmail.com', 'Female', '1981-04-17', '123-456-7890'),
('Noah', 'Xavier', 'noah.xavier@gmail.com', 'Male', '1999-07-30', '456-789-0123'),
('Zoe', 'Yates', 'zoe.yates@gmail.com', 'Female', '1988-02-12', '789-012-3456'),
('Liam', 'Anderson', 'liam.anderson@gmail.com', 'Male', '1997-05-18', '890-123-4567'),
('Scarlett', 'Barnes', 'scarlett.barnes@gmail.com', 'Female', '1986-10-31', '234-567-8901'),
('Ezra', 'Cooper', 'ezra.cooper@gmail.com', 'Male', '1983-08-14', '567-890-1234'),
('Aurora', 'Dixon', 'aurora.dixon@gmail.com', 'Female', '1991-02-27', '123-456-7890'),
('Oscar', 'Evans', 'oscar.evans@gmail.com', 'Male', '1994-12-10', '456-789-0123'),
('Nova', 'Ferguson', 'nova.ferguson@gmail.com', 'Female', '1980-05-23', '789-012-3456'),
('William', 'Gordon', 'william.gordon@gmail.com', 'Male', '1987-11-05', '901-234-5678'),
('Aria', 'Hamilton', 'aria.hamilton@gmail.com', 'Female', '1984-03-18', '234-567-8901'),
('Elijah', 'Isaacs', 'elijah.isaacs@gmail.com', 'Male', '1996-09-30', '567-890-1234'),
('Isabel', 'Jensen', 'isabel.jensen@gmail.com', 'Female', '1999-04-12', '123-456-7890'),
('Henry', 'Knox', 'henry.knox@gmail.com', 'Male', '1981-07-25', '345-678-9012'),
('Ava', 'Lancaster', 'ava.lancaster@gmail.com', 'Female', '1993-01-06', '678-901-2345'),
('Oliver', 'Morrison', 'oliver.morrison@gmail.com', 'Male', '1989-06-18', '890-123-4567'),
('Zoe', 'Nelson', 'zoe.nelson@gmail.com', 'Female', '1992-12-31', '234-567-8901');

--Inserting Sample of Transactions Records
INSERT INTO Transactions
( BookID, UserID, CheckoutDate, ReturnDate)
VALUES (42, 88, '2022-06-28', '2022-07-03'),
(90, 19, '2022-06-28', '2022-07-01'),
(99, 71, '2022-06-28', '2022-07-04'),
(67, 32, '2022-06-28', '2022-07-06'),
(10, 40, '2022-06-28', '2022-07-08'),
(78, 27, '2022-06-28', '2022-07-09'),
(33, 49, '2022-06-28', '2022-07-07'),
(15, 66, '2022-06-28', '2022-07-10'),
(44, 9, '2022-06-28', '2022-07-02'),
(89, 55, '2022-06-28', '2022-07-05'),
(27, 32, '2022-06-28', '2022-07-06'),
(66, 40, '2022-06-28', '2022-07-05'),
(21, 55, '2022-06-28', '2022-07-01'),
(8, 66, '2022-06-28', '2022-07-03'),
(55, 9, '2022-06-28', '2022-07-07'),
(36, 71, '2022-06-28', '2022-07-08'),
(117, 88, '2022-06-28', '2022-07-04'),
(17, 19, '2022-06-28', '2022-07-09'),
(49, 27, '2022-06-28', '2022-07-02'),
(71, 49, '2022-06-28', '2022-07-10'),
(93, 66, '2022-06-28', '2022-07-01'),
(14, 40, '2022-06-28', '2022-07-04'),
(75, 27, '2022-06-28', '2022-07-08'),
(112, 49, '2022-06-28', '2022-07-05'),
(50, 88, '2022-06-28', '2022-07-09'),
(22, 19, '2022-06-28', '2022-07-03'),
(90, 71, '2022-06-28', '2022-07-06'),
(99, 9, '2022-06-28', '2022-07-10'),
(67, 55, '2022-06-28', '2022-07-02'),
(10, 32, '2022-06-28', '2022-07-07'),
(4, 55, '2022-07-01', '2022-07-09'),
(17, 19, '2022-07-01', '2022-07-05'),
(30, 71, '2022-07-01', '2022-07-06'),
(42, 40, '2022-07-01', '2022-07-07'),
(55, 9, '2022-07-01', '2022-07-02'),
(68, 88, '2022-07-01', '2022-07-10'),
(80, 32, '2022-07-01', '2022-07-03'),
(93, 66, '2022-07-01', '2022-07-08'),
(106, 9, '2022-07-01', '2022-07-04'),
(119, 55, '2022-07-01', '2022-07-01'),
(15, 19, '2022-07-06', '2022-07-12'),
(44, 71, '2022-07-06', '2022-07-09'),
(89, 88, '2022-07-06', '2022-07-11'),
(27, 40, '2022-07-06', '2022-07-07'),
(66, 32, '2022-07-06', '2022-07-08'),
(21, 66, '2022-07-06', '2022-07-14'),
(8, 9, '2022-07-06', '2022-07-10'),
(55, 55, '2022-07-06', '2022-07-13'),
(17, 88, '2022-07-07', '2022-07-10'),
(49, 40, '2022-07-07', '2022-07-15'),
(71, 32, '2022-07-07', '2022-07-14'),
(93, 66, '2022-07-07', '2022-07-12'),
(14, 9, '2022-07-07', '2022-07-11'),
(75, 71, '2022-07-07', '2022-07-13'),
(112, 88, '2022-07-07', '2022-07-16'),
(50, 19, '2022-07-07', '2022-07-09'),
(22, 55, '2022-07-07', '2022-07-08'),
(90, 40, '2022-07-07', '2022-07-12'),
(99, 9, '2022-07-08', '2022-07-15'),
(67, 55, '2022-07-08', '2022-07-11'),
(10, 19, '2022-07-08', '2022-07-10'),
(78, 71, '2022-07-08', '2022-07-14'),
(33, 88, '2022-07-08', '2022-07-09'),
(15, 40, '2022-07-08', '2022-07-12'),
(44, 32, '2022-07-08', '2022-07-13'),
(89, 66, '2022-07-08', '2022-07-16'),
(27, 9, '2022-07-08', '2022-07-08'),
(66, 55, '2022-07-08', '2022-07-14'),
(21, 88, '2022-07-11', '2022-07-18'),
(8, 40, '2022-07-11', '2022-07-19'),
(55, 32, '2022-07-11', '2022-07-14'),
(36, 66, '2022-07-11', '2022-07-16'),
(112, 9, '2022-07-12', '2022-07-20'),
(75, 55, '2022-07-12', '2022-07-16'),
(50, 19, '2022-07-12', '2022-07-14'),
(22, 71, '2022-07-12', '2022-07-15'),
(90, 88, '2022-07-12', '2022-07-18'),
(99, 40, '2022-07-12', '2022-07-17'),
(67, 32, '2022-07-12', '2022-07-23'),
(10, 66, '2022-07-12', '2022-07-19'),
(78, 9, '2022-07-12', '2022-07-13'),
(33, 55, '2022-07-12', '2022-07-22'),
(15, 40, '2022-07-15', '2022-07-21'),
(44, 32, '2022-07-15', '2022-07-18'),
(89, 66, '2022-07-15', '2022-07-23'),
(17, 88, '2022-07-18', '2022-07-25'),
(49, 40, '2022-07-18', '2022-07-26'),
(71, 32, '2022-07-18', '2022-07-23'),
(93, 66, '2022-07-18', '2022-07-24'),
(14, 9, '2022-07-18', '2022-07-20'),
(75, 71, '2022-07-18', '2022-07-21'),
(112, 88, '2022-07-18', '2022-07-28'),
(99, 9, '2022-07-20', '2022-07-27'),
(67, 55, '2022-07-20', '2022-07-26'),
(10, 19, '2022-07-20', '2022-07-25'),
(78, 71, '2022-07-20', '2022-07-30'),
(33, 88, '2022-07-20', '2022-07-24'),
(15, 40, '2022-07-20', '2022-07-29'),
(21, 88, '2022-07-22', '2022-07-30'),
(8, 40, '2022-07-22', '2022-07-29'),
(55, 32, '2022-07-22', '2022-08-01'),
(36, 66, '2022-07-22', '2022-07-27'),
(117, 9, '2022-07-22', '2022-07-31'),
(17, 71, '2022-07-22', '2022-07-26'),
(49, 88, '2022-07-22', '2022-08-02'),
(90, 88, '2022-07-26', '2022-08-05'),
(99, 40, '2022-07-26', '2022-07-29'),
(67, 32, '2022-07-26', '2022-08-01'),
(10, 66, '2022-07-26', '2022-07-28'),
(78, 9, '2022-07-26', '2022-08-02'),
(89, 66, '2022-07-29', '2022-08-03'),
(27, 9, '2022-07-29', '2022-08-01'),
(66, 55, '2022-07-29', '2022-08-04'),
(21, 19, '2022-07-29', '2022-08-02'),
(8, 71, '2022-07-29', '2022-08-08'),
(55, 88, '2022-07-29', '2022-08-07'),
(17, 88, '2022-08-03', '2022-08-10'),
(49, 40, '2022-08-03', '2022-08-11'),
(71, 32, '2022-08-03', '2022-08-09'),
(93, 66, '2022-08-03', '2022-08-07'),
(14, 9, '2022-08-03', '2022-08-08'),
(75, 71, '2022-08-03', '2022-08-05'),
(99, 9, '2022-08-08', '2022-08-14'),
(67, 55, '2022-08-08', '2022-08-17'),
(10, 19, '2022-08-08', '2022-08-13'),
(78, 71, '2022-08-08', '2022-08-16'),
(36, 66, '2022-08-10', '2022-08-15'),
(117, 9, '2022-08-10', '2022-08-12'),
(17, 71, '2022-08-10', '2022-08-20'),
(49, 88, '2022-08-10', '2022-08-13'),
(71, 19, '2022-08-10', '2022-08-19'),
(93, 55, '2022-08-10', '2022-08-17'),
(22, 71, '2022-08-16', '2022-08-23'),
(90, 88, '2022-08-16', '2022-08-26'),
(99, 40, '2022-08-16', '2022-08-20'),
(67, 32, '2022-08-16', '2022-08-21'),
(10, 66, '2022-08-16', '2022-08-19'),
(44, 32, '2022-08-19', '2022-08-27'),
(89, 66, '2022-08-19', '2022-08-23'),
(27, 9, '2022-08-19', '2022-08-21'),
(66, 55, '2022-08-19', '2022-08-24'),
(21, 19, '2022-08-19', '2022-08-22'),
(8, 71, '2022-08-19', '2022-08-30'),
(17, 88, '2022-08-23', '2022-08-30'),
(49, 40, '2022-08-23', '2022-08-31'),
(71, 32, '2022-08-23', '2022-08-29'),
(93, 66, '2022-08-23', '2022-08-27'),
(14, 9, '2022-08-23', '2022-08-28'),
(75, 71, '2022-08-23', '2022-08-26'),
(67, 55, '2022-08-25', '2022-09-04'),
(10, 19, '2022-08-25', '2022-09-07'),
(78, 71, '2022-08-25', '2022-09-06'),
(33, 88, '2022-08-25', '2022-09-08'),
(15, 40, '2022-08-25', '2022-09-10'),
(44, 32, '2022-08-25', '2022-09-03'),
(89, 66, '2022-08-25', '2022-09-02'),
(27, 9, '2022-08-25', '2022-09-09'),
(66, 55, '2022-08-25', '2022-09-05'),
(8, 40, '2022-08-26', '2022-09-04'),
(55, 32, '2022-08-26', '2022-09-01'),
(36, 66, '2022-08-26', '2022-09-03'),
(117, 9, '2022-08-26', '2022-09-10'),
(17, 71, '2022-08-26', '2022-09-09'),
(49, 88, '2022-08-26', '2022-09-08'),
(71, 19, '2022-08-26', '2022-09-07'),
(93, 55, '2022-08-26', '2022-09-06'),
(22, 71, '2022-09-02', '2022-09-08'),
(90, 88, '2022-09-02', '2022-09-07'),
(99, 40, '2022-09-02', '2022-09-06'),
(67, 32, '2022-09-02', '2022-09-05'),
(10, 66, '2022-09-02', '2022-09-04'),
(78, 9, '2022-09-02', '2022-09-03'),
(15, 40, '2022-09-07', '2022-09-12'),
(44, 32, '2022-09-07', '2022-09-16'),
(89, 66, '2022-09-07', '2022-09-15'),
(27, 9, '2022-09-07', '2022-09-13'),
(66, 55, '2022-09-07', '2022-09-14'),
(21, 19, '2022-09-07', '2022-09-13'),
(8, 71, '2022-09-07', '2022-09-20'),
(17, 88, '2022-09-09', '2022-09-16'),
(49, 40, '2022-09-09', '2022-09-17'),
(71, 32, '2022-09-09', '2022-09-15'),
(93, 66, '2022-09-09', '2022-09-13'),
(14, 9, '2022-09-09', '2022-09-14'),
(75, 71, '2022-09-09', '2022-09-12'),
(112, 88, '2022-09-09', '2022-09-23'),
(50, 19, '2022-09-09', '2022-09-11'),
(99, 9, '2022-09-16', '2022-09-23'),
(67, 55, '2022-09-16', '2022-09-26'),
(10, 19, '2022-09-16', '2022-09-29'),
(78, 71, '2022-09-16', '2022-09-28'),
(33, 88, '2022-09-16', '2022-09-30'),
(15, 40, '2022-09-16', '2022-10-02'),
(44, 32, '2022-09-16', '2022-09-25'),
(89, 66, '2022-09-16', '2022-09-24'),
(21, 40, '2022-09-20', '2022-09-27'),
(8, 32, '2022-09-20', '2022-09-26'),
(55, 66, '2022-09-20', '2022-09-25'),
(36, 9, '2022-09-20', '2022-09-24'),
(117, 55, '2022-09-20', '2022-09-23'),
(17, 19, '2022-09-20', '2022-09-22'),
(49, 71, '2022-09-20', '2022-09-29'),
(71, 88, '2022-09-20', '2022-09-28'),
(93, 40, '2022-09-20', '2022-09-30'),
(55, 66, '2022-09-20', '2022-09-25'),
(36, 9, '2022-09-20', '2022-09-24'),
(117, 55, '2022-09-20', '2022-09-23'),
(17, 19, '2022-09-20', '2022-09-22'),
(49, 71, '2022-09-20', '2022-09-29'),
(71, 88, '2022-09-20', '2022-09-28'),
(93, 40, '2022-09-20', '2022-09-30'),
(67, 40, '2022-09-23', '2022-10-08'),
(10, 32, '2022-09-23', '2022-10-09'),
(78, 66, '2022-09-23', '2022-10-10'),
(33, 9, '2022-09-23', '2022-10-11'),
(15, 55, '2022-09-27', '2022-10-15'),
(44, 19, '2022-09-27', '2022-10-14'),
(89, 71, '2022-09-27', '2022-10-13'),
(27, 88, '2022-09-27', '2022-10-12'),
(66, 40, '2022-09-27', '2022-10-11'),
(21, 32, '2022-09-27', '2022-10-10'),
(8, 66, '2022-09-27', '2022-10-09'),
(55, 9, '2022-09-27', '2022-10-08'),
(17, 71, '2022-09-29', '2022-10-18'),
(49, 88, '2022-09-29', '2022-10-17'),
(71, 40, '2022-09-29', '2022-10-16'),
(93, 32, '2022-09-29', '2022-10-15'),
(14, 66, '2022-09-29', '2022-10-14'),
(75, 9, '2022-09-29', '2022-10-13'),
(112, 55, '2022-09-29', '2022-10-12'),
(50, 19, '2022-09-29', '2022-10-11'),
(22, 71, '2022-09-29', '2022-10-10'),
(67, 32, '2022-10-04', '2022-10-22'),
(10, 66, '2022-10-04', '2022-10-21'),
(78, 9, '2022-10-04', '2022-10-20'),
(33, 55, '2022-10-04', '2022-10-19'),
(15, 19, '2022-10-04', '2022-10-18'),
(44, 71, '2022-10-04', '2022-10-17'),
(89, 88, '2022-10-04', '2022-10-16'),
(50, 71, '2022-10-07', '2022-10-26'),
(22, 88, '2022-10-07', '2022-10-25'),
(90, 40, '2022-10-07', '2022-10-24'),
(99, 32, '2022-10-07', '2022-10-23'),
(67, 66, '2022-10-07', '2022-10-22'),
(10, 9, '2022-10-07', '2022-10-21'),
(78, 55, '2022-10-07', '2022-10-20'),
(33, 19, '2022-10-07', '2022-10-19'),
(15, 71, '2022-10-10', '2022-10-31'),
(44, 88, '2022-10-10', '2022-10-30'),
(89, 40, '2022-10-10', '2022-10-29'),
(27, 32, '2022-10-10', '2022-10-28'),
(66, 66, '2022-10-10', '2022-10-27'),
(21, 9, '2022-10-10', '2022-10-26'),
(8, 55, '2022-10-10', '2022-10-25'),
(55, 19, '2022-10-10', '2022-10-24'),
(17, 40, '2022-10-12', '2022-11-02'),
(49, 32, '2022-10-12', '2022-11-01'),
(71, 66, '2022-10-12', '2022-10-31'),
(93, 9, '2022-10-12', '2022-10-30'),
(14, 55, '2022-10-12', '2022-10-29'),
(75, 19, '2022-10-12', '2022-10-28'),
(112, 71, '2022-10-12', '2022-10-27'),
(10, 55, '2022-10-13', '2022-11-01'),
(78, 19, '2022-10-13', '2022-10-31'),
(33, 71, '2022-10-13', '2022-10-30'),
(15, 88, '2022-10-13', '2022-10-29'),
(44, 40, '2022-10-13', '2022-10-28'),
(89, 32, '2022-10-13', '2022-10-27'),
(27, 66, '2022-10-13', '2022-10-26'),
(66, 9, '2022-10-13', '2022-10-25'),
(21, 88, '2022-10-26', '2022-11-05'),
(8, 40, '2022-10-26', '2022-11-04'),
(55, 32, '2022-10-26', '2022-11-03'),
(36, 66, '2022-10-26', '2022-11-02'),
(117, 9, '2022-10-26', '2022-11-01'),
(17, 55, '2022-10-26', '2022-11-08'),
(49, 19, '2022-10-26', '2022-11-07'),
(75, 66, '2022-11-07', '2022-11-17'),
(50, 9, '2022-11-07', '2022-11-16'),
(22, 55, '2022-11-07', '2022-11-15'),
(90, 19, '2022-11-07', '2022-11-14'),
(99, 71, '2022-11-07', '2022-11-13'),
(67, 88, '2022-11-07', '2022-11-12'),
(10, 40, '2022-11-07', '2022-11-11'),
(78, 32, '2022-11-07', '2022-11-10'),
(15, 9, '2022-11-09', '2022-11-20'),
(44, 55, '2022-11-09', '2022-11-19'),
(89, 19, '2022-11-09', '2022-11-18'),
(27, 71, '2022-11-09', '2022-11-17'),
(66, 88, '2022-11-09', '2022-11-16'),
(21, 40, '2022-11-09', '2022-11-15'),
(8, 32, '2022-11-09', '2022-11-14'),
(55, 66, '2022-11-09', '2022-11-13'),
(17, 88, '2022-11-10', '2022-11-21'),
(49, 40, '2022-11-10', '2022-11-20'),
(71, 32, '2022-11-10', '2022-11-19'),
(93, 66, '2022-11-10', '2022-11-18'),
(14, 9, '2022-11-10', '2022-11-17'),
(75, 55, '2022-11-10', '2022-11-16'),
(112, 19, '2022-11-10', '2022-11-15'),
(50, 71, '2022-11-10', '2022-11-14'),
(99, 32, '2022-11-14', '2022-11-25'),
(67, 66, '2022-11-14', '2022-11-24'),
(10, 9, '2022-11-14', '2022-11-23'),
(78, 55, '2022-11-14', '2022-11-22'),
(33, 19, '2022-11-14', '2022-11-21'),
(15, 71, '2022-11-14', '2022-11-20'),
(44, 88, '2022-11-14', '2022-11-19'),
(89, 40, '2022-11-14', '2022-11-18'),
(27, 32, '2022-11-14', '2022-11-17'),
(8, 55, '2022-11-16', '2022-11-26'),
(55, 19, '2022-11-16', '2022-11-25'),
(36, 71, '2022-11-16', '2022-11-24'),
(117, 88, '2022-11-16', '2022-11-23'),
(17, 40, '2022-11-16', '2022-11-22'),
(49, 32, '2022-11-16', '2022-11-21'),
(71, 66, '2022-11-16', '2022-11-20'),
(93, 9, '2022-11-16', '2022-11-19'),
(112, 19, '2022-11-18', '2022-11-29'),
(50, 71, '2022-11-18', '2022-11-28'),
(22, 88, '2022-11-18', '2022-11-27'),
(90, 40, '2022-11-18', '2022-11-26'),
(99, 32, '2022-11-18', '2022-11-25'),
(67, 66, '2022-11-18', '2022-11-24'),
(10, 9, '2022-11-18', '2022-11-23'),
(78, 55, '2022-11-18', '2022-11-22'),
(44, 88, '2022-11-24', '2022-12-05'),
(89, 40, '2022-11-24', '2022-12-04'),
(27, 32, '2022-11-24', '2022-12-03'),
(66, 66, '2022-11-24', '2022-12-02'),
(21, 9, '2022-11-24', '2022-12-01'),
(8, 55, '2022-11-24', '2022-11-30'),
(55, 19, '2022-11-24', '2022-11-29'),
(49, 32, '2022-11-28', '2022-12-09'),
(71, 66, '2022-11-28', '2022-12-08'),
(93, 9, '2022-11-28', '2022-12-07'),
(14, 55, '2022-11-28', '2022-12-06'),
(75, 19, '2022-11-28', '2022-12-05'),
(112, 71, '2022-11-28', '2022-12-04'),
(50, 88, '2022-11-28', '2022-12-03'),
(22, 40, '2022-11-28', '2022-12-02'),
(90, 32, '2022-11-28', '2022-12-01'),
(67, 9, '2022-12-01', '2022-12-12'),
(10, 55, '2022-12-01', '2022-12-11'),
(78, 19, '2022-12-01', '2022-12-10'),
(33, 71, '2022-12-01', '2022-12-09'),
(15, 88, '2022-12-01', '2022-12-08'),
(44, 40, '2022-12-01', '2022-12-07'),
(8, 88, '2022-12-06', '2022-12-17'),
(55, 40, '2022-12-06', '2022-12-16'),
(36, 32, '2022-12-06', '2022-12-15'),
(117, 66, '2022-12-06', '2022-12-14'),
(90, 55, '2022-12-07', '2022-12-15'),
(99, 19, '2022-12-07', '2022-12-14'),
(67, 88, '2022-12-07', '2022-12-13'),
(44, 55, '2022-12-08', '2022-12-19'),
(89, 19, '2022-12-08', '2022-12-18'),
(27, 71, '2022-12-08', '2022-12-17'),
(66, 88, '2022-12-08', '2022-12-16'),
(21, 40, '2022-12-08', '2022-12-15'),
(8, 32, '2022-12-08', '2022-12-14'),
(55, 66, '2022-12-08', '2022-12-13'),
(36, 9, '2022-12-08', '2022-12-12'),
(117, 55, '2022-12-08', '2022-12-11'),
(75, 66, '2023-01-06', '2023-01-13'),
(71, 88, '2023-01-06', '2023-01-16'),
(93, 40, '2023-01-06', '2023-01-15'),
(14, 32, '2023-01-06', '2023-01-14'),
(49, 71, '2023-01-06', '2023-01-17'),
(71, 88, '2023-01-06', '2023-01-16'),
(93, 40, '2023-01-06', '2023-01-15'),
(14, 32, '2023-01-06', '2023-01-14'),
(75, 66, '2023-01-06', '2023-01-13'),
(112, 9, '2023-01-06', '2023-01-12'),
(50, 55, '2023-01-06', '2023-01-11'),
(22, 19, '2023-01-06', '2023-01-10'),
(90, 71, '2023-01-06', '2023-01-09'),
(67, 40, '2023-01-09', '2023-01-20'),
(10, 32, '2023-01-09', '2023-01-19'),
(78, 66, '2023-01-09', '2023-01-18'),
(33, 9, '2023-01-09', '2023-01-17'),
(15, 55, '2023-01-09', '2023-01-16'),
(44, 19, '2023-01-09', '2023-01-15'),
(89, 71, '2023-01-09', '2023-01-14'),
(27, 88, '2023-01-09', '2023-01-13'),
(8, 66, '2023-01-11', '2023-01-22'),
(55, 9, '2023-01-11', '2023-01-21'),
(36, 55, '2023-01-11', '2023-01-20'),
(117, 19, '2023-01-11', '2023-01-19'),
(17, 71, '2023-01-11', '2023-01-18'),
(49, 88, '2023-01-11', '2023-01-17'),
(71, 40, '2023-01-11', '2023-01-16'),
(93, 32, '2023-01-11', '2023-01-15'),
(22, 88, '2023-01-12', '2023-01-21'),
(90, 40, '2023-01-12', '2023-01-20'),
(99, 32, '2023-01-12', '2023-01-19'),
(67, 66, '2023-01-12', '2023-01-18'),
(10, 9, '2023-01-12', '2023-01-17'),
(78, 55, '2023-01-12', '2023-01-16'),
(33, 19, '2023-01-12', '2023-01-15'),
(89, 40, '2023-01-16', '2023-01-26'),
(27, 32, '2023-01-16', '2023-01-25'),
(66, 66, '2023-01-16', '2023-01-24'),
(21, 9, '2023-01-16', '2023-01-23'),
(8, 55, '2023-01-16', '2023-01-22'),
(55, 19, '2023-01-16', '2023-01-21'),
(36, 71, '2023-01-16', '2023-01-20'),
(117, 88, '2023-01-16', '2023-01-19'),
(49, 71, '2023-01-18', '2023-01-29'),
(71, 88, '2023-01-18', '2023-01-28'),
(49, 88, '2022-09-29', '2022-10-17'),
(71, 40, '2022-09-29', '2022-10-16'),
(93, 32, '2022-09-29', '2022-10-15'),
(14, 66, '2022-09-29', '2022-10-14'),
(75, 9, '2022-09-29', '2022-10-13'),
(112, 55, '2022-09-29', '2022-10-12'),
(50, 19, '2022-09-29', '2022-10-11'),
(22, 71, '2022-09-29', '2022-10-10'),
(67, 32, '2022-10-04', '2022-10-22'),
(10, 66, '2022-10-04', '2022-10-21'),
(78, 9, '2022-10-04', '2022-10-20'),
(33, 55, '2022-10-04', '2022-10-19'),
(15, 19, '2022-10-04', '2022-10-18'),
(44, 71, '2022-10-04', '2022-10-17'),
(89, 88, '2022-10-04', '2022-10-16'),
(50, 71, '2022-10-07', '2022-10-26'),
(22, 88, '2022-10-07', '2022-10-25'),
(90, 40, '2022-10-07', '2022-10-24'),
(99, 32, '2022-10-07', '2022-10-23'),
(67, 66, '2022-10-07', '2022-10-22'),
(10, 9, '2022-10-07', '2022-10-21'),
(78, 55, '2022-10-07', '2022-10-20'),
(33, 19, '2022-10-07', '2022-10-19'),
(15, 71, '2022-10-10', '2022-10-31'),
(44, 88, '2022-10-10', '2022-10-30'),
(89, 40, '2022-10-10', '2022-10-29'),
(27, 32, '2022-10-10', '2022-10-28'),
(66, 66, '2022-10-10', '2022-10-27'),
(21, 9, '2022-10-10', '2022-10-26'),
(8, 55, '2022-10-10', '2022-10-25'),
(55, 19, '2022-10-10', '2022-10-24'),
(17, 40, '2022-10-12', '2022-11-02'),
(49, 32, '2022-10-12', '2022-11-01'),
(71, 66, '2022-10-12', '2022-10-31'),
(93, 9, '2022-10-12', '2022-10-30'),
(14, 55, '2022-10-12', '2022-10-29'),
(75, 19, '2022-10-12', '2022-10-28'),
(112, 71, '2022-10-12', '2022-10-27'),
(10, 55, '2022-10-13', '2022-11-01'),
(78, 19, '2022-10-13', '2022-10-31'),
(33, 71, '2022-10-13', '2022-10-30'),
(15, 88, '2022-10-13', '2022-10-29'),
(44, 40, '2022-10-13', '2022-10-28'),
(89, 32, '2022-10-13', '2022-10-27'),
(27, 66, '2022-10-13', '2022-10-26'),
(66, 9, '2022-10-13', '2022-10-25'),
(21, 88, '2022-10-26', '2022-11-05'),
(8, 40, '2022-10-26', '2022-11-04'),
(55, 32, '2022-10-26', '2022-11-03'),
(36, 66, '2022-10-26', '2022-11-02'),
(117, 9, '2022-10-26', '2022-11-01'),
(17, 55, '2022-10-26', '2022-11-08'),
(49, 19, '2022-10-26', '2022-11-07'),
(75, 66, '2022-11-07', '2022-11-17'),
(50, 9, '2022-11-07', '2022-11-16'),
(22, 55, '2022-11-07', '2022-11-15'),
(90, 19, '2022-11-07', '2022-11-14'),
(99, 71, '2022-11-07', '2022-11-13'),
(67, 88, '2022-11-07', '2022-11-12'),
(10, 40, '2022-11-07', '2022-11-11'),
(78, 32, '2022-11-07', '2022-11-10'),
(15, 9, '2022-11-09', '2022-11-20'),
(44, 55, '2022-11-09', '2022-11-19'),
(89, 19, '2022-11-09', '2022-11-18'),
(27, 71, '2022-11-09', '2022-11-17'),
(66, 88, '2022-11-09', '2022-11-16'),
(21, 40, '2022-11-09', '2022-11-15'),
(8, 32, '2022-11-09', '2022-11-14'),
(55, 66, '2022-11-09', '2022-11-13'),
(17, 88, '2022-11-10', '2022-11-21'),
(49, 40, '2022-11-10', '2022-11-20'),
(71, 32, '2022-11-10', '2022-11-19'),
(93, 66, '2022-11-10', '2022-11-18'),
(14, 9, '2022-11-10', '2022-11-17'),
(75, 55, '2022-11-10', '2022-11-16'),
(112, 19, '2022-11-10', '2022-11-15'),
(50, 71, '2022-11-10', '2022-11-14'),
(99, 32, '2022-11-14', '2022-11-25'),
(67, 66, '2022-11-14', '2022-11-24'),
(10, 9, '2022-11-14', '2022-11-23'),
(78, 55, '2022-11-14', '2022-11-22'),
(33, 19, '2022-11-14', '2022-11-21'),
(15, 71, '2022-11-14', '2022-11-20'),
(44, 88, '2022-11-14', '2022-11-19'),
(89, 40, '2022-11-14', '2022-11-18'),
(27, 32, '2022-11-14', '2022-11-17'),
(8, 55, '2022-11-16', '2022-11-26'),
(55, 19, '2022-11-16', '2022-11-25'),
(36, 71, '2022-11-16', '2022-11-24'),
(117, 88, '2022-11-16', '2022-11-23'),
(17, 40, '2022-11-16', '2022-11-22'),
(49, 32, '2022-11-16', '2022-11-21'),
(71, 66, '2022-11-16', '2022-11-20'),
(93, 9, '2022-11-16', '2022-11-19'),
(112, 19, '2022-11-18', '2022-11-29'),
(50, 71, '2022-11-18', '2022-11-28'),
(22, 88, '2022-11-18', '2022-11-27'),
(90, 40, '2022-11-18', '2022-11-26'),
(99, 32, '2022-11-18', '2022-11-25'),
(67, 66, '2022-11-18', '2022-11-24'),
(10, 9, '2022-11-18', '2022-11-23'),
(78, 55, '2022-11-18', '2022-11-22'),
(44, 88, '2022-11-24', '2022-12-05'),
(89, 40, '2022-11-24', '2022-12-04'),
(27, 32, '2022-11-24', '2022-12-03'),
(66, 66, '2022-11-24', '2022-12-02'),
(21, 9, '2022-11-24', '2022-12-01'),
(8, 55, '2022-11-24', '2022-11-30'),
(55, 19, '2022-11-24', '2022-11-29'),
(49, 32, '2022-11-28', '2022-12-09'),
(71, 66, '2022-11-28', '2022-12-08'),
(93, 9, '2022-11-28', '2022-12-07'),
(14, 55, '2022-11-28', '2022-12-06'),
(75, 19, '2022-11-28', '2022-12-05'),
(112, 71, '2022-11-28', '2022-12-04'),
(50, 88, '2022-11-28', '2022-12-03'),
(22, 40, '2022-11-28', '2022-12-02'),
(90, 32, '2022-11-28', '2022-12-01'),
(67, 9, '2022-12-01', '2022-12-12'),
(10, 55, '2022-12-01', '2022-12-11'),
(78, 19, '2022-12-01', '2022-12-10'),
(33, 71, '2022-12-01', '2022-12-09'),
(15, 88, '2022-12-01', '2022-12-08'),
(44, 40, '2022-12-01', '2022-12-07'),
(8, 88, '2022-12-06', '2022-12-17'),
(55, 40, '2022-12-06', '2022-12-16'),
(36, 32, '2022-12-06', '2022-12-15'),
(117, 66, '2022-12-06', '2022-12-14'),
(90, 55, '2022-12-07', '2022-12-15'),
(99, 19, '2022-12-07', '2022-12-14'),
(67, 88, '2022-12-07', '2022-12-13'),
(44, 55, '2022-12-08', '2022-12-19'),
(89, 19, '2022-12-08', '2022-12-18'),
(27, 71, '2022-12-08', '2022-12-17'),
(66, 88, '2022-12-08', '2022-12-16'),
(21, 40, '2022-12-08', '2022-12-15'),
(8, 32, '2022-12-08', '2022-12-14'),
(55, 66, '2022-12-08', '2022-12-13'),
(36, 9, '2022-12-08', '2022-12-12'),
(117, 55, '2022-12-08', '2022-12-11'),
(75, 66, '2023-01-06', '2023-01-13'),
(71, 88, '2023-01-06', '2023-01-16'),
(93, 40, '2023-01-06', '2023-01-15'),
(14, 32, '2023-01-06', '2023-01-14'),
(49, 71, '2023-01-06', '2023-01-17'),
(71, 88, '2023-01-06', '2023-01-16'),
(93, 40, '2023-01-06', '2023-01-15'),
(14, 32, '2023-01-06', '2023-01-14'),
(75, 66, '2023-01-06', '2023-01-13'),
(112, 9, '2023-01-06', '2023-01-12'),
(50, 55, '2023-01-06', '2023-01-11'),
(22, 19, '2023-01-06', '2023-01-10'),
(90, 71, '2023-01-06', '2023-01-09'),
(67, 40, '2023-01-09', '2023-01-20'),
(10, 32, '2023-01-09', '2023-01-19'),
(78, 66, '2023-01-09', '2023-01-18'),
(33, 9, '2023-01-09', '2023-01-17'),
(15, 55, '2023-01-09', '2023-01-16'),
(44, 19, '2023-01-09', '2023-01-15'),
(89, 71, '2023-01-09', '2023-01-14'),
(27, 88, '2023-01-09', '2023-01-13'),
(8, 66, '2023-01-11', '2023-01-22'),
(55, 9, '2023-01-11', '2023-01-21'),
(36, 55, '2023-01-11', '2023-01-20'),
(117, 19, '2023-01-11', '2023-01-19'),
(17, 71, '2023-01-11', '2023-01-18'),
(49, 88, '2023-01-11', '2023-01-17'),
(71, 40, '2023-01-11', '2023-01-16'),
(93, 32, '2023-01-11', '2023-01-15'),
(22, 88, '2023-01-12', '2023-01-21'),
(90, 40, '2023-01-12', '2023-01-20'),
(99, 32, '2023-01-12', '2023-01-19'),
(67, 66, '2023-01-12', '2023-01-18'),
(10, 9, '2023-01-12', '2023-01-17'),
(78, 55, '2023-01-12', '2023-01-16'),
(33, 19, '2023-01-12', '2023-01-15'),
(89, 40, '2023-01-16', '2023-01-26'),
(27, 32, '2023-01-16', '2023-01-25'),
(66, 66, '2023-01-16', '2023-01-24'),
(21, 9, '2023-01-16', '2023-01-23'),
(8, 55, '2023-01-16', '2023-01-22'),
(55, 19, '2023-01-16', '2023-01-21'),
(36, 71, '2023-01-16', '2023-01-20'),
(117, 88, '2023-01-16', '2023-01-19'),
(49, 71, '2023-01-18', '2023-01-29'),
(71, 88, '2023-01-18', '2023-01-28'),
(93, 40, '2023-01-18', '2023-01-27'),
(14, 32, '2023-01-18', '2023-01-26'),
(75, 66, '2023-01-18', '2023-01-25'),
(112, 9, '2023-01-18', '2023-01-24'),
(50, 55, '2023-01-18', '2023-01-23'),
(67, 40, '2023-01-23', '2023-02-03'),
(10, 32, '2023-01-23', '2023-02-02'),
(78, 66, '2023-01-23', '2023-02-01'),
(33, 9, '2023-01-23', '2023-01-31'),
(15, 55, '2023-01-23', '2023-01-30'),
(44, 19, '2023-01-23', '2023-01-29'),
(89, 71, '2023-01-23', '2023-01-28'),
(27, 88, '2023-01-23', '2023-01-27'),
(66, 40, '2023-01-23', '2023-01-26'),
(8, 66, '2023-01-24', '2023-02-04'),
(55, 9, '2023-01-24', '2023-02-03'),
(36, 55, '2023-01-24', '2023-02-02'),
(117, 19, '2023-01-24', '2023-02-01'),
(17, 71, '2023-01-24', '2023-01-31'),
(49, 88, '2023-01-24', '2023-01-30'),
(71, 40, '2023-01-24', '2023-01-29'),
(93, 32, '2023-01-24', '2023-01-28'),
(10, 71, '2023-01-27', '2023-01-31'),
(78, 88, '2023-01-27', '2023-01-30'),
(33, 40, '2023-01-27', '2023-01-29'),
(15, 32, '2023-01-27', '2023-01-28'),
(44, 9, '2023-02-01', '2023-02-11'),
(89, 55, '2023-02-01', '2023-02-10'),
(27, 19, '2023-02-01', '2023-02-09'),
(66, 71, '2023-02-01', '2023-02-08'),
(21, 88, '2023-02-01', '2023-02-07'),
(8, 40, '2023-02-01', '2023-02-06'),
(55, 32, '2023-02-01', '2023-02-05'),
(36, 66, '2023-02-01', '2023-02-04'),
(49, 71, '2023-02-03', '2023-02-13'),
(71, 88, '2023-02-03', '2023-02-12'),
(93, 40, '2023-02-03', '2023-02-11'),
(14, 32, '2023-02-03', '2023-02-10'),
(75, 66, '2023-02-03', '2023-02-09'),
(112, 9, '2023-02-03', '2023-02-08'),
(50, 55, '2023-02-03', '2023-02-07'),
(22, 19, '2023-02-03', '2023-02-06'),
(10, 32, '2023-02-07', '2023-02-16'),
(78, 66, '2023-02-07', '2023-02-15'),
(33, 9, '2023-02-07', '2023-02-14'),
(15, 55, '2023-02-07', '2023-02-13'),
(44, 19, '2023-02-07', '2023-02-12'),
(89, 71, '2023-02-07', '2023-02-11'),
(27, 88, '2023-02-07', '2023-02-10'),
(66, 40, '2023-02-07', '2023-02-09'),
(36, 55, '2023-02-09', '2023-02-17'),
(117, 19, '2023-02-09', '2023-02-16'),
(17, 71, '2023-02-09', '2023-02-15'),
(49, 88, '2023-02-09', '2023-02-14'),
(71, 40, '2023-02-09', '2023-02-13'),
(93, 32, '2023-02-09', '2023-02-12'),
(14, 66, '2023-02-09', '2023-02-11'),
(36, 55, '2023-02-09', '2023-02-17'),
(117, 19, '2023-02-09', '2023-02-16'),
(17, 71, '2023-02-09', '2023-02-15'),
(49, 88, '2023-02-09', '2023-02-14'),
(71, 40, '2023-02-09', '2023-02-13'),
(93, 32, '2023-02-09', '2023-02-12'),
(14, 66, '2023-02-09', '2023-02-11'),
(99, 40, '2023-02-10', '2023-02-16'),
(67, 32, '2023-02-10', '2023-02-15'),
(10, 66, '2023-02-10', '2023-02-14'),
(78, 9, '2023-02-10', '2023-02-13'),
(33, 55, '2023-02-10', '2023-02-12'),
(15, 19, '2023-02-10', '2023-02-11'),
(44, 88, '2023-02-16', '2023-02-26'),
(89, 40, '2023-02-16', '2023-02-25'),
(27, 32, '2023-02-16', '2023-02-24'),
(66, 66, '2023-02-16', '2023-02-23'),
(21, 9, '2023-02-16', '2023-02-22'),
(8, 55, '2023-02-16', '2023-02-21'),
(55, 19, '2023-02-16', '2023-02-20'),
(36, 71, '2023-02-16', '2023-02-19'),
(117, 88, '2023-02-16', '2023-02-18'),
(22, 32, '2023-02-17', '2023-02-20'),
(90, 66, '2023-02-17', '2023-02-19'),
(99, 9, '2023-02-17', '2023-02-18'),
(67, 55, '2023-02-21', '2023-03-03'),
(10, 19, '2023-02-21', '2023-03-02'),
(78, 71, '2023-02-21', '2023-03-01'),
(33, 88, '2023-02-21', '2023-02-28'),
(15, 40, '2023-02-21', '2023-02-27'),
(44, 32, '2023-02-21', '2023-02-26'),
(89, 66, '2023-02-21', '2023-02-25'),
(27, 9, '2023-02-21', '2023-02-24'),
(66, 55, '2023-02-21', '2023-02-23'),
(36, 66, '2023-02-23', '2023-03-03'),
(117, 9, '2023-02-23', '2023-03-02'),
(17, 55, '2023-02-23', '2023-03-01'),
(49, 19, '2023-02-23', '2023-02-28'),
(71, 71, '2023-02-23', '2023-02-27'),
(93, 88, '2023-02-23', '2023-02-26'),
(112, 9, '2023-02-27', '2023-03-09'),
(50, 55, '2023-02-27', '2023-03-08'),
(22, 19, '2023-02-27', '2023-03-07'),
(90, 71, '2023-02-27', '2023-03-06'),
(99, 88, '2023-02-27', '2023-03-05'),
(67, 40, '2023-02-27', '2023-03-04'),
(10, 32, '2023-02-27', '2023-03-03'),
(78, 66, '2023-02-27', '2023-03-02'),
(33, 9, '2023-02-27', '2023-03-01'),
(27, 32, '2023-03-01', '2023-03-10'),
(66, 66, '2023-03-01', '2023-03-09'),
(21, 9, '2023-03-01', '2023-03-08'),
(8, 55, '2023-03-01', '2023-03-07'),
(55, 19, '2023-03-01', '2023-03-06'),
(36, 71, '2023-03-01', '2023-03-05'),
(117, 88, '2023-03-01', '2023-03-04'),
(49, 32, '2023-03-03', '2023-03-14'),
(71, 66, '2023-03-03', '2023-03-13'),
(93, 9, '2023-03-03', '2023-03-12'),
(14, 55, '2023-03-03', '2023-03-11'),
(75, 19, '2023-03-03', '2023-03-10'),
(112, 40, '2023-03-03', '2023-03-09'),
(50, 32, '2023-03-03', '2023-03-08'),
(10, 40, '2023-03-06', '2023-03-16'),
(78, 32, '2023-03-06', '2023-03-15'),
(33, 66, '2023-03-06', '2023-03-14'),
(15, 9, '2023-03-06', '2023-03-13'),
(44, 55, '2023-03-06', '2023-03-12'),
(89, 19, '2023-03-06', '2023-03-11'),
(27, 40, '2023-03-06', '2023-03-10'),
(66, 32, '2023-03-06', '2023-03-09'),
(8, 9, '2023-03-07', '2023-03-18'),
(55, 55, '2023-03-07', '2023-03-17'),
(36, 19, '2023-03-07', '2023-03-16'),
(117, 71, '2023-03-07', '2023-03-15'),
(17, 88, '2023-03-07', '2023-03-14'),
(49, 40, '2023-03-07', '2023-03-13'),
(71, 32, '2023-03-07', '2023-03-12'),
(93, 66, '2023-03-07', '2023-03-11'),
(14, 9, '2023-03-07', '2023-03-10'),
(67, 71, '2023-03-09', '2023-03-15'),
(10, 88, '2023-03-09', '2023-03-14'),
(78, 40, '2023-03-09', '2023-03-13'),
(33, 32, '2023-03-09', '2023-03-12'),
(15, 66, '2023-03-09', '2023-03-11'),
(44, 9, '2023-03-10', '2023-03-21'),
(89, 55, '2023-03-10', '2023-03-20'),
(27, 19, '2023-03-10', '2023-03-19'),
(66, 40, '2023-03-10', '2023-03-18'),
(21, 32, '2023-03-10', '2023-03-17'),
(8, 66, '2023-03-10', '2023-03-16'),
(55, 9, '2023-03-10', '2023-03-15'),
(36, 55, '2023-03-10', '2023-03-14'),
(49, 88, '2023-03-14', '2023-03-25'),
(71, 40, '2023-03-14', '2023-03-24'),
(93, 32, '2023-03-14', '2023-03-23'),
(14, 66, '2023-03-14', '2023-03-22'),
(75, 9, '2023-03-14', '2023-03-21'),
(112, 55, '2023-03-14', '2023-03-20'),
(50, 19, '2023-03-14', '2023-03-19'),
(22, 40, '2023-03-14', '2023-03-18'),
(49, 88, '2023-03-14', '2023-03-25'),
(71, 40, '2023-03-14', '2023-03-24'),
(93, 32, '2023-03-14', '2023-03-23'),
(14, 66, '2023-03-14', '2023-03-22'),
(75, 9, '2023-03-14', '2023-03-21'),
(112, 55, '2023-03-14', '2023-03-20'),
(50, 19, '2023-03-14', '2023-03-19'),
(22, 40, '2023-03-14', '2023-03-18'),
(90, 32, '2023-03-14', '2023-03-17'),
(67, 9, '2023-03-15', '2023-03-26'),
(10, 55, '2023-03-15', '2023-03-25'),
(78, 19, '2023-03-15', '2023-03-24'),
(33, 40, '2023-03-15', '2023-03-23'),
(15, 32, '2023-03-15', '2023-03-22'),
(44, 66, '2023-03-15', '2023-03-21'),
(89, 9, '2023-03-15', '2023-03-20'),
(27, 55, '2023-03-15', '2023-03-19');

--Task 4: Implement Data Modification
--Descri--Task 4: Implement Data Modification
--Description: Create SQL scripts for updating, deleting, and modifying data as needed.ption: Create SQL scripts for updating, deleting, and modifying data as needed.

--USING CTE AND ROW NUMBER
--Deleting duplicate records from a table of Books
WITH CTE_DuplicateRecords AS (
SELECT
BookTitle,
BookAuthor,
ISBN,
PublishedYear,
Publisher,
Language,
Pages,
GenreID,
ROW_NUMBER() OVER (
PARTITION BY BookTitle, BookAuthor, ISBN, PublishedYear, Publisher, Language, Pages, GenreID
ORDER BY BookID
) AS DuplicateRecords
FROM Books
)
DELETE FROM CTE_DuplicateRecords WHERE DuplicateRecords > 1;

--Deleting Sunday in ReturnDate Table (Library is Closed)
/* Guide for DATEPART & DATENAME
SELECT DATEPART(yy, GETDATE()) AS Year;
SELECT DATEPART(qq, GETDATE()) AS Quarter;
SELECT DATEPART(mm, GETDATE()) AS Month;
SELECT DATEPART(dd, GETDATE()) AS DayOfMonth;
SELECT DATEPART(dw, GETDATE()) AS WeekdayNumber;
SELECT DATENAME(dy, GETDATE()) AS WeekdayNameAbbreviated;
SELECT DATENAME(month, GETDATE()) AS MonthName;
SELECT DATENAME(year, GETDATE()) AS YearName;
*/

WITH CTE_DeleteSunday_Records AS (
SELECT
ReturnDate,
CASE
WHEN DATENAME(dw, ReturnDate) = 'Sunday' THEN 'Sunday'
ELSE 'It is not a Sunday'
END AS DayStatus
FROM Transactions
)
DELETE FROM CTE_DeleteSunday_Records WHERE DayStatus = 'Sunday'

ALTER TABLE Books
ALTER COLUMN PublishedYear INT NOT NULL;(--From DATE change into INT) --encounter some error Msg 206, Level 16, State 2, Line 22 --Operand type clash: int is incompatible with date

--PHASE 3: DATA RETRIEVAL (DQL)
--Task 5: Create SQL Queries for Common Tasks
--Description: Develop SQL queries to perform common library-related tasks, such as searching for books by title, author, or genre.

--Books that Published of Year 2000 onwards
SELECT
BookTitle,
BookAuthor,
PublishedYear,
PubLisher
FROM Books
WHERE
PublishedYear >= '2000';--There is 117 PublishedYear 2000 onwards

--Otherwise Books that Published earlier than 2000
SELECT
BookTitle,
BookAuthor,
PublishedYear,
PubLisher
FROM Books
WHERE
PublishedYear <= '2000';--There is 18 published book earlier than 2000

--Books that language is English
SELECT
BookTitle,
BookAuthor,
PublishedYear,
Language
FROM Books
WHERE Language = 'English' ;-- From execute query there is 131 Language

--Books that language is Spanish
SELECT
BookTitle,
BookAuthor,
PublishedYear,
Language
FROM Books
WHERE Language = 'Spanish' ; --From execute query there is 4 Spanish Language
--Total Counts of BookAuthor
SELECT
BookTitle
BookAuthor,
COUNT( BookAuthor) OVER (PARTITION BY BookAuthor) AS TotalBookAuthor
FROM Books
GROUP BY BookTitle, BookAuthor
ORDER BY TotalBookAuthor DESC;
--Query to Search for books by title
SELECT
BookTitle,
BookAuthor,
PublishedYear,
PubLisher
FROM Books
WHERE BookAuthor LIKE '%Scott%'
--Total Counts of Users by Gender
SELECT
Gender,
COUNT(Gender) AS TotalGender
FROM Users
GROUP BY Gender;

--Task 6: Implement Advanced Queries
--Description: Construct more advanced SQL queries for generating reports on borrowed books, overdue books, and popular book genres.

--Query to generate a report on Books borrowed by Users (Days between for Over due status)
SELECT
CheckoutDate,
ReturnDate,
DATEDIFF(DAY, CheckoutDate, ReturnDate) AS DaysBetween,
CASE
WHEN DATEDIFF(DAY, CheckoutDate, ReturnDate) > 7 THEN 'Overdue'
WHEN DATEDIFF(DAY, CheckoutDate, ReturnDate) = 0 THEN 'Same day'
ELSE 'Not Overdue'
END AS OverdueStatus
FROM Transactions
ORDER BY DaysBetween DESC;

--Query to generate Borrowed books, ReturnDate (OverdueStatus),
SELECT
BookTitle,
BookAuthor,
CheckOutDate,
ReturnDate,
DATEDIFF(DAY, CheckoutDate, ReturnDate) AS DaysBetween,
CASE
WHEN DATEDIFF(DAY, CheckoutDate, ReturnDate) > 7 THEN 'Overdue'
WHEN DATEDIFF(DAY, CheckoutDate, ReturnDate) = 0 THEN 'Same day'
ELSE 'Not Overdue'
END AS OverdueStatus,
FirstName + ' ' + LastName AS FullName
FROM Books
JOIN Transactions ON Books.BookID = Transactions.BookID
JOIN Users ON Transactions.UserID = Users.UserID
ORDER BY DaysBetween DESC;

--Query report on Books ft. Genre
SELECT
BookTitle,
BookAuthor,
PublishedYear,
PubLisher,
Genre
FROM Books
JOIN Genres ON Books.BookID = Genres.GenreID

--Query to generate a report on Popular books that borrowed by User
SELECT
b.BookTitle,
b.BookAuthor,
b.PublishedYear,
b.PubLisher,
FirstName + ' ' + LastName AS FullName,
Gender,
COUNT(b.BookId) AS TotalUsers
FROM Books AS b
JOIN Transactions AS txn
ON b.BookID = txn.BookID
JOIN Users
ON txn.UserID = Users.UserID
GROUP BY b.BookTitle,
b.BookAuthor,
b.PublishedYear,
b.PubLisher,
FirstName,
LastName,
Gender
ORDER BY TotalUsers DESC;

--TOP 10 Genre that popular to borrowed
SELECT TOP 10
b.BookTitle,
b.BookAuthor,
g.Genre
FROM Books AS b
LEFT JOIN Genres AS g ON b.BookID = g.GenreID
LEFT JOIN Transactions AS txn ON b.BookID = txn.BookID
LEFT JOIN Users AS u ON txn.UserID = u.UserID;

--PHASE 4: ACCESS CONTROL (DCL)
--Task 7: Define User Roles and Permissions
--Description: Define user roles (e.g., librarian, member) and set appropriate permissions.

/*
Identify User Roles:
1.) List the different roles that users will have in the system. For example:
Librarian
Member
Administrator
2.)Define Responsibilities for Each Role:
--The responsibilities and tasks associated with each role.
Librarian: Manage book inventory, add new books, update book information.
Member: Borrow and return books, view personal borrowing history.
Administrator: Manage user accounts, configure system settings.
3.)Assign Permissions to Roles:
Identifying the specific actions/operations that users in each role should be able to perform:
--Librarian:
Create, read, update, and delete (CRUD) operations on the book database.
Generate reports on book inventory.
--Member:
Borrow and return books.
View personal borrowing history.
--Administrator:
Create and manage user accounts.
Configure system settings.
*/

USE LIBRARY;

-- Create User Roles
CREATE ROLE Librarian;
CREATE ROLE Member;
CREATE ROLE Administrator;

--Task 8: Implement Access Control Statements
--Description: Implement DCL statements to control access to the database based on user roles and permissions.

-- Grant Permissions to Librarian Role
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Books TO Librarian;

-- Grant Permissions to Member Role
GRANT SELECT ON dbo.Books TO Member;

-- Grant Permissions to Administrator Role
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Books TO Administrator;

--Task 9: Simulate User Interactions
--Description: Simulate user interactions with the system, demonstrating how access control works based on user roles.
USE LIBRARY;
--Create table login
CREATE TABLE Login (
Username NVARCHAR(50),
Password NVARCHAR(50)
);
INSERT INTO Login ( Username, Password)
VALUES
('Administrator', 'Admin1'),
('Librarian', 'Librarian1'),
('Member', 'Member1');

--Store Procedure
CREATE PROCEDURE sp_login
@Username NVARCHAR(50),
@Password NVARCHAR(50)
AS
BEGIN
SELECT *
FROM Login
WHERE Username = @Username
AND Password = @Password
END;
--

USE LIBRARY
SELECT name, type_desc, create_date
FROM sys.server_principals
WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP');

-- Simulate User Administrator
USE master
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UserAdministrator')
CREATE LOGIN UserAdministrator WITH PASSWORD = 'UserAdministrator';
USE Library
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Admin1')
CREATE USER Admin1 FOR LOGIN UserAdministrator;
-- Create the 'Administrator' role if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Administrator')
CREATE ROLE Administrator;
EXEC sp_addrolemember 'Administrator', 'Admin1';
-- Simulate User Librarian
USE master
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UserLibrarian')
CREATE LOGIN UserLibrarian WITH PASSWORD = 'UserLibrarian';
USE Library
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Librarian1')
CREATE USER Librarian1 FOR LOGIN UserLibrarian;
-- Create the 'Librarian' role if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Librarian')
CREATE ROLE Librarian;
EXEC sp_addrolemember 'Librarian', 'Librarian1';
-- Simulate User Member
USE master
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UserMember')
CREATE LOGIN UserMember WITH PASSWORD = 'UserMember';
USE Library
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Member1')
CREATE USER Member1 FOR LOGIN UserMember;
-- Create the 'Member' role if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Member')
CREATE ROLE Member;
EXEC sp_addrolemember 'Member', 'Member1';

--PHASE 5: USER INTERFACE (OPTIONAL)
--Task 10: Create a Power BI Report
--Description: (Optional) Develop a Power BI dashboard to interact with the SQL Server database. Enhance the project's practicality.
--PHASE 6: DOCUMENTATION AND PRESENTATION
--Task 11: Prepare Documentation
--Description: Document the entire project, including a project report, schema diagrams, SQL scripts, and explanations of DML, DDL, DQL, and DCL operations.

/* DOCUMENTATION
The Books table stores information about books, it includes a unique BookID, title, author, ISBN, and a foreign key GenreID referencing the Genres table.
The Genres table stores different book genres, identified by a unique GenreID.
The Transactions table represents transactions such as book checkouts and returns. It includes a unique TransactionID, BookID foreign key referencing the Books table, UserID foreign key referencing the Users table, and dates for checkout and return.
The Users table stores information about library users, including a unique UserID, first name, last name, phone, email, and a foreign key RoleID referencing the Roles Table.
In SSMS (Auto Increment or Identity) refers to a property that can be assigend to a column in a table.
*Generates a unique, sequential numeric value for each new row.
*Can be applied to integer or numeric columns and inserted without requiring explicit user interventions.
DQL(Data Query Language) – DQL Commands are used to retrieve data from the database, example of DQL Commands is SELECT.
DDL(Data Definition Language) – DDL Commands are used to alter, edit and managing the attributes of the tables, example of DDL commands is CREATE, ALTER , DROP AND TRUNCATE;
DML(Data Manipulation Language) – DML commands are used to manipulate the data, example of DML command is INSERT, UPDATE and DELETE (IN ADDITONAL CALL, EXPLAIN CALL, AND LOCK;
DCL(Data Control Language) – DCL Commands are used to control the access of the database, example of DCL commands is GRANT and REVOKE
*/
--TASK 12: PRESENT THE PROJECT
--Description: Present the project to the class, highlighting the different SQL Server features and operations learned during the project's development.

Introduction:

A Personal Money Tracker is designed to help individuals manage and monitor their finances. It assists in keeping track of income, expenses, savings, investments etc. in order to achieve financial goals and maintain a healthy financial situation.

Situation — In light of recent theory discussions and a masterclass session led by a prominent mentor within our community, a pivotal project has emerged: the development of a Personal Finance Manager. This undertaking represents a significant responsibility, requiring the establishment of our unique capabilities to deliver an all-encompassing financial management solution. The project bears great significance as it not only serves as a foundational endeavour but also necessitates a comprehensive inception from the ground up. This entails the meticulous gathering of pertinent data, followed by a thorough analysis aimed at extracting data-driven insights. These insights, in turn, are poised to play a dual role — optimizing our financial landscape and guiding strategic decision-making processes. The depth and scope of this project underscore its importance in advancing our expertise and fostering a profound impact within the realm of personal finance management.

Task/Problem — Draw a data-driven insight: Set of data entries or values that have been provided by User Columns (Catalog) it includes various types of data such as text, numbers, dates, selections from predefined options or even files.
Calculated columns( Amount, Sub-category, Category, Main type and trim data wrangling for Month).

Action/Objective & Solution — Use of MS Excel To:
Clean Data, Transform Data, Lookup Value, Data Mapping, Merge data table, and create new dimensions to meet the requirements. Ready for the data analysis, such as exploratory data analysis (EDA), numbering crunching using pivot table etc.

Results/Benefits —

• Interactive and dynamic dashboard at a glance
• Visualized Income Goal Summary
• In-depth Monthly Income and Expenses Analysis
• Enhanced Interaction with Income Sources
• Flexible Reporting Periods Granular Expense Breakdown with Filtering Capabilities
• Accumulated wealth (Current balance/cash on hand) tracker on a year-to-date basis Monthly, multi-month selection, and year-to-date report period
• Month over Month (current month vs. previous month) variance analysis
• Manage sub-categories by if default category
• Envelope System(Necessities expenses such as food, housing, bills, transportation, etc. Financial obligation expenses such as education, self-improvement, insurance, etc. Lifestyle choice expenses such as travel, dining out, gadgets, games, etc.)
• List of Expenses by sub-category with interactive category filtering insights
• Planned Method ( 50,20,30 rule)
• Smart Financial Advisory Intuitively User-Friendly Interface
• Effortless Data Entry, Even for High Volume Transactions

One of the most impactful projects that I gained proficiency in through the DataSense Analytics Bootcamp Community is:

Here's a step-by-step list to guide you through the process:

1. ) Planning

• Get to know the context, and workflow system process.
• Ask effective questions
• Define problems
• Define objectives and solutions
• Define the Metrics & KPI’s

2.) Data Preparation

• Identify the source of the Data
• Extract, Transform & Load (ETL)
• Data Mining, Data Transformation, Data Cleaning and Data Wrangling
• Defining New Dimensions and measures tomeet business needs.
• Data storage, privacy & security

3.) Data Modeling

• Identify Data Entries and definetable relationships.
• Create Data schemas
• Data normalizations.

4.) Data Analysis

• Filter Sort & group data
• Aggregate data
• Analyze the metrics and KPI’s
• Identify or uncover patterns, trends, and Insights
• Make Predictions

5.) Data Visualization

• Bring Data to life
• Create effective visuals.
• By Use of Data Storytelling
• Make Driven Decisions and recommendations

6.) Deployment and Maintenance

• Publish and Share your Insights.
• Solve problem
• Act based on data-driven decisions/Recommendations
• Measure Feedback
• Improve your project

All rights reserved_2023©

I would greatly appreciate any corrections and suggestions related to my post that can help me enhance my Data Analysis and Visualization knowledge. I am glad you found my post useful, and I invite you to like or clap it. Please follow me for more updates in the future.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and look over my repository post here:
https://www.medium.com/@toledo.alejandro

Preface:

In 2016, Cyclistic introduced a highly successful bike-share program, which has experienced significant expansion ever since. The program currently boasts a fleet of 5,824 bicycles that are equipped with tracking capabilities and securely docked at a network of 692 stations spread across the city of Chicago. An integral feature of this system is the flexibility it offers to users, allowing them to unlock bikes from one station and conveniently return them to any other station within the network at their convenience.

Cyclistic’s marketing strategy focused on generating widespread awareness and targeting a broad range of consumer segments. A key factor that contributed to this success was the flexibility of their pricing options, which included single-ride passes, full-day passes, and annual memberships. Customers who opt for single-ride or full-day passes are classified as casual riders, while those who choose annual memberships become Cyclistic members.

Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders.

Ms Moreno, Marketing Manager, has set a clear goal to Design marketing strategies aimed at converting casual riders into annual members. In order to do that, the marketing analyst team needs to better understand how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics. As Junior Data Analyst in the Marketing Team, I was assigned to understand how casual riders and annual members use Cyclistic Bikes Differently.

Scenario/Task Description:

In order to answer the key business questions, will follow the steps of the data analysis process:

Ask, Prepare, Process, Analyze, Share, And Act.

ASK:

Key tasks:

1. Identify the business task

2. Consider key stakeholders

Three questions will guide the future marketing program:

1. How do annual members and casual riders use Cyclistic bikes differently?

2. Why would casual riders buy Cyclistic annual memberships?

3. How can Cyclistic use digital media to influence casual riders to become members?

PREPARE:

Key tasks 1. Download data and store it appropriately.

2. Identify how it’s organized.

Will use Cyclistic’s historical trip data to analyze and identify trends. The source data were downloaded from 12 months of Cyclistic Trip Data (April 2022 to May 2023). Excel File.csv Cyclistic Trips Data has been provided through this link. The datasets have a different name because Cyclistic is a fictional company.

ROCCC Objective Data Analysis:

By considering these factors of ROCCC analysis, ascertain that the objective data used for analyzing Cyclistic’s historical trip trends are reliable, original, comprehensive, current, and appropriately cited.

Reliable: The data provided by Motivate International Inc. is considered reliable as it is sourced from the company itself and provided under this license.

Original: The data used in the analysis is original, meaning it has not been altered or manipulated and represents the actual historical trip data of Cyclistic.

Comprehensive: The dataset provided in the Excel file.csv includes a comprehensive range of information related to Cyclistic’s trips, allowing for a thorough analysis of various aspects and trends. This is public data that can be used to explore how different customer types use Cyclistic bikes. But

NOTE: that data-privacy issues prohibit from using riders’ personally identifiable information.

Current: The data covers the time period from April 2022 to May 2023, ensuring that the analysis is based on relatively recent and up-to-date information.

Cited: Proper citation and acknowledgement of the data source, Motivate International Inc. should be included when using or referencing the dataset for analysis.

PROCESS

Key tasks 1. Check the data for errors.

2. Choose a tool.

3. Transform the data so can work with it effectively.

Using PostgreSQL PgAdmin4 to import the downloaded multiple-CSV files/data.

a.) Data Collection — Gather the raw data from sources.

Create a Table if not exists public.cyclistrips_cleaned
-- Table: public.cyclistrips_cleaned
-- DROP TABLE IF EXISTS public.cyclistrips_cleaned;

CREATE TABLE IF NOT EXISTS public.cyclistrips_cleaned
(
    ride_id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    rideable_type character varying(50) COLLATE pg_catalog."default",
    started_at date,
    ended_at date,
    start_station_name character varying(255) COLLATE pg_catalog."default",
    end_station_name character varying(255) COLLATE pg_catalog."default",
    start_lat numeric NOT NULL,
    start_lng numeric NOT NULL,
    end_lat numeric NOT NULL,
    end_lng numeric NOT NULL,
    member_casual character varying(255) COLLATE pg_catalog."default" NOT NULL,
    start_time interval NOT NULL,
    end_time interval NOT NULL,
    ride_length interval,
    times_of_day character varying(10) COLLATE pg_catalog."default",
    seasons character varying(10) COLLATE pg_catalog."default",
    CONSTRAINT cyclistrips_cleaned_pkey PRIMARY KEY (ride_id)
);

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.cyclistrips_cleaned
    OWNER to Postgres;

b. ) Data Integration — Merge data from multiple sources, ensuring that the data is compatible and can be combined properly. This may involve matching and merging records based on a common identifier.

— Consolidated the 12 months of Cyclistic Trip Data (April 2022 to May 2023) in folder/zip.

In order to union/import all data files,

· Check the properties of the folder, then go to security Object name: D:\Users\Desktop\CyclisticTripData.

· Edit and add the User name (Everyone) together with authenticated users, allowing full control

· From the folder properties go to security and get the object name of CSV.

— Open PostgreSQL (PgAdmin4) — into query copy and import multiple CSV. Files.

— import-multiple-CSV-files-to-a-postgres-table-using-pgadmin.

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202205-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202206-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202207-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202208-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202209-divvy-publictripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202210-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202211-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202212-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202301-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202302-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202303-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

COPY cyclistictrips FROM 'D:\Users\huawie\Desktop\CyclisticTripData\202304-divvy-tripdata.csv'
DELIMITER ',' CSV HEADER;

c.) Data Cleaning — Identify and handle missing data, outliers, and inconsistencies. This may involve tasks like removing duplicate records, handling missing values, and correcting data entry errors.

— Filter out rows with null values in the specified columns. (For checking)

 — Removing inconsistencies in data. Sometimes there might be duplicated entries.
SELECT *
INTO Cyclistrips_CLeaned
FROM (
  SELECT *
  FROM cyclistictrips
  WHERE start_station_name IS NOT NULL
    AND end_station_name IS NOT NULL
    AND start_station_id IS NOT NULL
    AND end_station_id IS NOT NULL
    AND start_lat IS NOT NULL
    AND end_lat IS NOT NULL
    AND start_lng IS NOT NULL
    AND end_lng IS NOT NULL
) AS subquery;

  --Checking for Refresh
  SELECT * FROM CYCLISTRIPS_cleaned
  order by year desc

d.) Data Transformation — Convert data into a suitable format for analysis. This includes tasks like standardizing data formats, encoding categorical variables, and normalizing numerical variables. It may also involve creating new variables or aggregating data at different levels of granularity.

SELECT *
FROM cyclistrips_cleaned
WHERE start_lat != end_lat
  AND start_lng != end_lng
  AND start_station_name != end_station_name
  AND EXTRACT(EPOCH FROM (ended_at - started_at)) > EXTRACT(EPOCH FROM '00:00:00'::interval)
order by started_at desc;

UPDATE cyclistrips_cleaned
SET ride_length = ended_at - started_at
WHERE start_lat != end_lat
  AND start_lng != end_lng
  AND start_station_name != end_station_name
  AND (ended_at - started_at) > INTERVAL '00:00:00)'
ORDER BY started_at DESC;

--table has a timestamp 
ALTER TABLE cyclistictrips
ADD COLUMN year text,
ADD COLUMN start_time TIME WITHOUT TIME ZONE,
ADD COLUMN end_time TIME WITHOUT TIME ZONE;

--Update
UPDATE cyclistictrips
SET year = EXTRACT(YEAR FROM started_at)
    start_time = EXTRACT(TIME WITHOUT TIME ZONE FROM started_at)
    and end_time = EXTRACT(TIME WITHOUT TIME ZONE FROM ended_at);

--Add the "ride_length" column and calculate the length of each ride:
ALTER TABLE Cyclistrips_Cleaned
ADD COLUMN ride_length INTERVAL;

--Update
UPDATE Cyclistrips_Cleaned
SET ride_length = ended_at - started_at;

--Updating column queries
UPDATE cyclistrips_cleaned
SET rideable_type = CASE
    WHEN rideable_type = 'classic_bike' THEN 'Classic Bike'
    WHEN rideable_type = 'docked_bike' THEN 'Docked Bike'
    WHEN rideable_type = 'electric_bike' THEN 'Electric Bike'
    ELSE rideable_type
END,
  member_casual = case 
 when member_casual = 'casual' then 'Casual'
 when member_casual = 'member' then 'Member'
 else member_casual
 end,

--Add a column for Operations & Season

ALTER TABLE Cyclistrips_Cleaned
ADD COLUMN times_of_day VARCHAR(10),
ADD COLUMN seasons VARCHAR(10);

UPDATE Cyclistrips_Cleaned
SET times_of_day =
    CASE
        WHEN start_time >= '06:00:00' AND start_time <= '12:00:00' THEN 'Morning'
        WHEN start_time >= '12:00:00' AND start_time <= '17:00:00' THEN 'Afternoon'
        WHEN start_time >= '17:00:00' AND start_time <= '00:00:00' THEN 'Evening'
        ELSE 'Midnight'
    END,
    seasons =
    CASE
        WHEN EXTRACT(MONTH FROM started_at) IN (12, 1, 2) THEN 'Winter'
        WHEN EXTRACT(MONTH FROM started_at) IN (3, 4, 5) THEN 'Spring'
        WHEN EXTRACT(MONTH FROM started_at) IN (6, 7, 8) THEN 'Summer'
        WHEN EXTRACT(MONTH FROM started_at) IN (9, 10, 11) THEN 'Autumn'
        ELSE 'Unknown'
    END;
--"Months of each season"
--Spring months: March, April, May.
--Summer months: June, July, August.
--Autumn (Fall months): September, October, November.
--Winter months: December, January, February

e.) Data Filtering — examining a dataset to exclude.

— This helps to focus on relevant data subsets for analysis, and remove unnecessary or irrelevant columns that are not needed for the analysis or modeling tasks at hand.

--Drop the start_station_id & end_station_id
ALTER TABLE cyclistrips_cleaned
DROP COLUMN start_station_id,
DROP COLUMN end_station_id;
--after data manipulation
DROP TABLE  cyclistictrips

select * from cyclistrips_cleaned
--Refresh

Using Power BI Desktop:

a.) Connect the PostgreSQL Database

b.) After connecting the PostgreSQL Database to Power BI Desktop.

c.) Go to Data Source Settings, edit the Credentials set up the User Name and Password
and lastly the Privacy Level.

— Then Refresh

d.) Load and Transform;

ANALYZE

Key tasks 1. Aggregate data so it’s useful and accessible.

2. Organize and format data.

3. Perform calculations.

4. Identify trends and relationships.

Data Formatting — the analysis or modeling techniques that will be applied.

--Write a DAX expression to create a new Date and chronological Order table.
Calendar = 
var
    _startdate = DATE(2022,05,01)
var _enddate   = DATE(2023,04,30)
return
    ADDCOLUMNS(
        CALENDAR(_startdate,_enddate)
        , "YEAR", YEAR([Date])
        , "MONTH NUMBER", MONTH([Date])
        , "MONTHCard", FORMAT([Date], "MMMM")
        , "MONTH", FORMAT([Date], "MMM")
        , "WEEKDAY NUMBER", WEEKDAY([Date],1)
        , "WEEK", FORMAT([Date], "DDD")
        , "WeekCard", FORMAT([Date], "DDDD")
        , "DAY", DAY([Date])
    )

— Notice that when creating a chart for times of day and season, the result is not in chronological order even in ascending or descending order.

— Create a Table for Sort Order for Times of Day and Season.

— For Modeling and Relationship

Create New Measure for DAX Formulas:

— for (Radial Bar Chart)

Total Rides = COUNT('public cyclistrips_cleaned'[ride_id]) 

Target Rides % = [Total Rides]/4533999

— Create a Diff. and Dummy measure

Diff = .75[Target Rides %]

DummyMeasure = .75 * (1/3)

— Create a Dax for the Subtitle

Subtitle = 
IF(
    COUNT('public cyclistrips_cleaned'[ride_id]) > 0,
    " Ride Frequency by User Category " & FORMAT(
        COUNT('public cyclistrips_cleaned'[ride_id]),
        "#,##0 "
    ),
    BLANK()
)

— Create a Dax for Card Text Color

CardColor = 
    IF(
        ISFILTERED(Times_Of_Day_SortOrder[Times_Of_Day]),
        SWITCH(
            VALUES(Times_Of_Day_SortOrder[Times_Of_Day]),
            "Midnight", "#3049AD",
            "Morning", "#00DFA2",
            "Afternoon", "#FF994E",
            "Evening", "#FF0060",
            "#E6E6E6"
        ),
        "#FFFFFF"
    )

— Create a Dax for the Card Visual

Total Rides = COUNT('public cyclistrips_cleaned'[ride_id]) 

Avg Ride Length(mins.) = AVERAGE('public cyclistrips_cleaned'[ride_length])

Busiest Month = 
VAR TopMonth =
    TOPN(1,
        SUMMARIZE('Calendar', 'Calendar'[MONTHCard]),
        [Total Rides]
    )
RETURN
    TopMonth

Busiest Season = 
VAR TopSeason =
    TOPN(1,
        SUMMARIZE(Season_SortOrder,Season_SortOrder[Season]),
        [Total Rides]
    )
RETURN
    TopSeason

Busiest Day = 
VAR TopDay =
    TOPN(1,
        SUMMARIZE('Calendar', 'Calendar'[WeekCard]),
        [Total Rides]
    )
RETURN
    TopDay

— Create a Dax for the Average Length(mins)

//Create a Highlight Color for Max & Min (for Bar Chart / Line Chart)

AvgRideLenght = CALCULATE(AVERAGE('public cyclistrips_cleaned'[ride_length]))

AvgLengthMax = MAXX(ALLSELECTED('Calendar'[WEEK],'Calendar'[WeekdayOrder]), [AvgRideLenght])

AvgLengthMin = MINX(ALLSELECTED('Calendar'[WEEK],'Calendar'[WeekdayOrder]), [AvgRideLenght])

— Create a Dax for the Rides Total

//Create a Highlight Color for Max & Min (for Bar Chart / Line Chart)

MaxRides = MAXX(ALLSELECTED('Calendar'[WEEK]), [RidesTotal])

MinRides = MINX(ALLSELECTED('Calendar'[WEEK]), [RidesTotal])

RidesTotalAvg = AVERAGEX(ALL('Calendar'[MONTH],'Calendar'[MonthOrder]), [RidesTotal])

— New features of Power BI, A measure tools format (Dynamic)

--Write a DAX Expression to create a Dynamic Format.
VAR _TotalRides = COUNT('public cyclistrips_cleaned'[ride_id])
RETURN
SWITCH(
    TRUE()
    , _TotalRides < 1000, "0k"
    , _TotalRides < 1000000, "#,.0K"
    , _TotalRides < 1000000000, "#,,.0M"
    , _TotalRides >=10000000000, "#,,,.0B"
)

//format (Dynamic) 
Rides AvgDynamic = 
VAR TotalRides =
    AVERAGEX(
        SUMMARIZE('Calendar', 'Calendar'[MONTH]),
        CALCULATE(
            COUNT('public cyclistrips_cleaned'[ride_id])
        )
    )
VAR HighlightedAvg =
    SWITCH(
        TRUE(),
        TotalRides < 1000, "0K",
        TotalRides < 1000000, "#,.0K",
        TotalRides < 1000000000, "#,,.0M",
        TotalRides >= 1000000000, "#,,,.0B"
    )
RETURN
    HighlightedAvg

— Arrangement of Dax Query into Folder
//to easily lookup the necessary field value.

Credits to Kerry Kolosko for the SVG Template Sparkline Code:

Spark_TotalRides = 
// Static line color - use %23 instead of # for Firefox compatibility (Measure Derived from Eldersveld Modified by Kolosko)
VAR LineColour = "#6BBCC0"
VAR PointColour = "#FF0060"
VAR Defs = "<defs>
    <linearGradient id='grad' x1='0' y1='25' x2='0' y2='50' gradientUnits='userSpaceOnUse'>
        <stop stop-color='#118DFF' offset='0' />
        <stop stop-color='#118DFF' offset='0.3' />
        <stop stop-color='white' offset='1' />
    </linearGradient>
</defs>"
// "Date" field used in this example along the X axis
VAR XMinDate = MIN('Calendar'[Date])
VAR XMaxDate = MAX('Calendar'[Date])

// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX(Values('Calendar'[Date]),CALCULATE([Total Rides]))
VAR YMaxValue = MAXX(Values('Calendar'[Date]),CALCULATE([Total Rides]))

// Build table of X & Y coordinates and fit to 50 x 150 viewbox
VAR SparklineTable = ADDCOLUMNS(
    SUMMARIZE('Calendar','Calendar'[Date]),
        "X",INT(150 * DIVIDE('Calendar'[Date] - XMinDate, XMaxDate - XMinDate)),
        "Y",INT(50 * DIVIDE([Total Rides] - YMinValue,YMaxValue - YMinValue)))

// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 50-[Y]," ", 'Calendar'[Date])

// Last data points on the line
VAR LastSparkYValue = MAXX( FILTER(SparklineTable, 'Calendar'[Date] = XMaxDate), [Y])
VAR LastSparkXValue = MAXX( FILTER(SparklineTable, 'Calendar'[Date] = XMaxDate), [X])

// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = 
    "data:image/svg+xml;utf8," & 
    --- gradient---
    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='-7 -7 164 64'>" & Defs & 
     "<polyline fill='url(#grad)' fill-opacity='0.3' stroke='transparent' 
      stroke-width='0' points=' 0 50 " & Lines & 
      " 150 150 Z '/>" &
    --- Lines---
    "<polyline 
        fill='transparent' stroke='" & LineColour & "' 
        stroke-linecap='round' stroke-linejoin='round' 
        stroke-width='3' points=' " & Lines & 
      " '/>" &
    --- Last Point---
        "<circle cx='"& LastSparkXValue & "' cy='" & 50 - LastSparkYValue & "' r='4' stroke='" & LineColour & "' stroke-width='3' fill='" & PointColour & "' />" &
        "</svg>"
RETURN SVGImageURL

— For Casual

TotalRidesCasual = CALCULATE(COUNT('public cyclistrips_cleaned'[ride_id]), KEEPFILTERS('public cyclistrips_cleaned'[member_casual]="casual"))

Target % = [TotalRidesCasual]/1791114

Target Diff = .75-[Target %]

Dummy_C = .75 * (1/3)

Insights:

●During SUMMER, there were 873.8K rides, which accounted for 19.27% of the target rides percentage. The distribution across the SUMMER months was JUNE–6.44%, JULY-6.87%, and August–5.96% of total frequency rides.

● During the month of JULY, the AFTERNOON hours experienced the highest volume of rides, reaching a total of 646.9K trips. This figure accounted for 14.27% of the overall target of total members.

●Among the weekdays, SATURDAY recorded the highest number of rides w/ a total of 362.8K trips. This figure was 8.00% higher than the lowest recorded rides on TUESDAY, which were 204.3K trips. TUESDAY rides accounted for 4.51% of shared rides. The Rides Average accounted for 149.26k.

●During SUNDAY, the average ride length was 26.52 minutes, making it the highest among the recorded days. And WEDNESDAYS, which was the lowest at 19.81 minutes. The Average Ride Length was 23 minutes & 15 seconds.

●Since Summer the Classic Bike was popular with 49.61% total, and the Electric bike with 41.02%, among other bikes some preferred to use a Classic bike for work purposes, while others preferred for work travel for ease upon arriving at the station. And for Electric Bikes and Docked bike for convenience or leisure time rides.

— For Member

TotalRidesMember = CALCULATE(COUNT('public cyclistrips_cleaned'[ride_id]), KEEPFILTERS('public cyclistrips_cleaned'[member_casual]="Member"))

Target_% = [TotalRidesMember]/2742885

Target_Diff = .75-[Target_%]

Dummy_M = .75 * (1/3)

Insights:

●During Summer, there were 994.5K rides, which accounted for 21.93% of the target rides percentage. The distribution across the SUMMER months was JUNE-7.24%, JULY- 7.30%, and AUGUST-7.39% of total frequency rides.

● During the month of JULY, the AFTERNOON hours experienced the highest volume of rides, reaching a total of 875.6K trips. This figure accounted for 19.31% of the overall target of total members.

●Among the weekdays, WEDNESDAY recorded the highest number of rides with a total of 440.4K trips. This figure was 9.71% higher than the lowest recorded rides on Sundays, which were 310.1K trips. Sunday rides accounted for a 6.85% share of the total number of rides. The Rides Average accounted for 228.57k.

●During SATURDAY, the average ride length was 13.77 minutes, making it the highest among the recorded days. And WEDNESDAYS, which was the lowest at 11.69 minutes. The Average Ride Length was 12 minutes & 25 seconds.

●Since Summer is a popular time for vacays and travel, whether it’s for leisure, or exploring new destinations the Classic Bike was popular with 63.82% total, and the Electric bike with 36.18%, among other bikes some preferred to use a Classic bike for work purposes, while others preferred for work travel for ease upon arriving at the station. And for Electric Bikes for convenience or leisure time rides.

Share/Act

Key Insights:
 — By User Category — For Member (2,742,885)-60.50% was higher than Casual (1,791,114)-39.50% out of 4.5 million rides.

— Total Rides = 4.53M
 — Average Ride Length 16 minutes and 56 seconds
 — Busiest Month — JULY (642.68K Rides | 14.17% Total)
 — Busiest Season — Summer (1.87M Rides | 41.21% Total) | Average Length Rides (18.61 minutes)
 — Busiest Day- Saturday (709.7K Rides | 15.65% Total) |

— The busiest time of day was the Afternoon (1.52M Rides | 33.58% Total) with an activity peak of 5 PM.

— During the Weekend surprising that Casual Rides increased the number of users, And for Member users the busiest rides were on Wednesday and Thursday are typically considered midweek days and many people commute to work during these days.

— Rideshare service offers a convenient and efficient for individual members to travel to and from their workplace especially if they prefer not to use their own vehicles.

— Saturday Ride Length reached its peak of 19.98 minutes, surpassing that of Wednesdays, which recorded the lowest ride length of 14.33 minutes, with a notable increase of 39.39%. Over the days of the week, the average ride length varied between 14.33 and 19.98 minutes, for a total of 16 minutes and 56 seconds. For both member and casual rides

— During the month of JULY, the Afternoon hours experienced the highest volume of rides, reaching a total of 1,522,526 million trips, this figure accounted for 33.58% of the overall target of total rides.
 — After Summer Season, Autumn is the next, Docked bikes will pretty surely arise, to increase the services for users who ride RENT for leisure time or bonding time.

Recommendations:

1. How do annual members and casual riders use Cyclistic bikes differently?

— Annual Members may use the bikes for daily commutes, or for transportation, while Casual riders use to occasionally or for leisure time such as short trips.

— Annual Members pay a yearly membership which allows the rider boundless access to bikes and routes and perks of discount, while Casual Riders usually pay per ride or per hour rent which can be more expensive and limited access if they use bikes frequently.

2.) Why would casual riders buy Cyclistic annual memberships?

— Can offer the Casual Riders the annual membership will get less hassle because annual membership can be more cost-effective compared to paying for pay per rides or per-hour rent or they can simply access the bikes anytime they want to access to it.

— Access to perks, benefits, discounts, or priority access to bike during peak hours.

3.) How can Cyclistic use digital media to influence casual riders to become members?

— By implementing a referral program, Cyclistic can offer rewards or discounts to current members who refer and can give perks, incentives or discounts for annual renewal.
 — By using data analysis to identify potential casual riders, and display advertisements (Highlight of Benefits of Annual Membership), such as cost-savings perks and convenience

This dataset was created for this project, subject to the terms and conditions.

All rights reserved_2023©

I would greatly appreciate any corrections and suggestions related to my post that can help me enhance my Data Analysis and Visualization knowledge. I am glad you found my post useful, and I invite you to like or clap it. Please follow me for more updates in the future.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and look over my repository post here:
https://www.medium.com/@toledo.alejandro

“Financial Dataset — By MSLearn (Modified by DSA)” V.1

“Financial Dataset Using Excel(Power Query Editor, AppendQueries, Pivot)”

Preface:

A manager wants to see a report on your latest sales figures. They’ve requested an executive summary of:

Scenario/Task Description:

— ASK:

Using the data gathered.
1. Which month and year had the most profit?

2. Where is the company seeing the most success (by country/region)?

3. Which product and segment should the company continue to invest in?

— PREPARE:

a.) Download the Dataset; Excel Spree

b.) To perform the analysis, I imported the data file from Datasense Analytics into Microsoft Excel;
a.) This data You may Download this dataset from the DSA Data Community Facebook Group. The dataset permalink is https://tinyurl.com/29aw79sp;

b.) Measures to Protect Data — This dataset was created for this project, subject to the terms and conditions.

— PROCESS:

Export, Transform, and Load to Excel

“Financial Dataset — By MSLearn (Modified by DSA)”.excel workbook.

· Using the Power Query editor, consolidated the data using an Append Queries function and then fixed the Data Type, header, columns, and number formatting. Also used an italic font to highlight the aggregated numbers for easier readability.

· Another way to consolidate the data;

a.) Open Data “Financial Dataset — By MSLearn (Modified by DSA)”.

b.) Use Power Query Editor (Blank Query),

= Excel.CurrentWorkbook()

= Table.Combine({tblCanada,tblFrance,tblGermany,tblMexico,tblUSA})

c.) Use the original column name as the prefix.

a.) Data Cleaning:
Segment: Text Type;
Country: Text Type;
Product: Text Type;
Discount Band: Text Type;

Units Sold: Whole Number; Manufacturing Price: Whole Number;

Sale Price: Whole Number;
Gross Sales: Whole Number;
Discounts: Decimal Number;
Sales: Decimal Number;
COGS: Whole Number;
Profit: Decimal Number;
Date: Date Type

b.) Transform & Load
 — Table Design
a.) Use a Light Gray Background 2, Darker 10%;

b.) Header Change to (Black Fill);(Font Color-Yellow);

c.) also used an italic font to highlight the aggregated numbers for easier readability.
C.) Using A PIVOT TABLE to Select the cells you want to create a PivotTable from;

Select Insert > PivotTable.

This will create a Pivot Table based on an existing table or range. …

Choose where you want the PivotTable report to be placed. …

Click OK.

ANALYZE:
Use data analysis tools (Spreadsheet, Power Query Editor, Pivot, DataViz) to draw conclusions
Exploratory Data Analysis (Sumifs, Sumif, Index + match /xlookup),

SHARE:

Act:
Done to dive into the problem;

a.) Key Insights:
1. Which month and year had the most profit?

— -Based on the financial data analyzed from January 2013 to December 2014, the month that recorded the highest sales was October 2014, with a total of $12,375,820. This was closely followed by December 2014, with a profit amount of $11,998,788.

— -And from September 2013 to December 2014. the month that recorded the highest profit was December 2014, with a total of $2,025,766.

2. Where is the company seeing the most success (by country/region)?

— -The company has achieved significant success in different countries and regions over the years. In 2013, Germany, Canada, and France recorded the highest sales and profits, while in 2014, the USA, Canada, and France topped the list in terms of sales and profits.

3. Which product and segment should the company continue to invest in?

— -After conducting a thorough analysis of the product and segment data from the Year-Month of 2013–2014, it appears that the Paseo product and Government segment have consistently generated the highest sales and profits. Therefore, it is recommended that the company continues to invest in these areas to maintain its success.

b.) Recommendation

 — -As a Data Analyst here are some recommendations:
To maximize profits,
●It is important for the company to conduct further analysis to identify the underlying factors that contributed to the high profits in October and December 2014. By understanding the key drivers of success in these months, the company can potentially replicate these factors in other months to sustain high profits throughout the year.

●The company should consider conducting a detailed market analysis to identify potential growth opportunities in other countries and regions. While Germany, Canada, France, and the USA have shown significant success, there may be untapped markets that the company can explore to diversify its revenue streams.

●In addition to investing in the Paseo product and Government segment, the company should consider diversifying its product portfolio and expanding into new segments. This can help to mitigate risks associated with relying heavily on a single product or segment, and can also open up new growth opportunities for the company. The company should conduct market research to identify potential areas for expansion and should allocate resources strategically to ensure that new products and segments are aligned with the company’s overall goals and objectives.

This dataset was created for this project, subject to the terms and conditions.

All rights reserved_2023©

I would greatly appreciate any corrections and suggestions related to my post that can help me enhance my Data Analysis and Visualization knowledge. I am glad you found my post useful, and I invite you to like or clap it. Please follow me for more updates in the future.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and look over my repository post here:
medium.com/@toledo.alejandro1995

“Calculating Business Metrics for A Delivery Company”

Preface:

Indian delivery company. Top managers want to expand the business to other countries in Asia, so they asked to make a report about delivery time to compare the company’s performance with competitors.

Scenario/Task Description:

A.) Preparing:

· Download the Dataset;

· Data is presented in a .csv file “Delivery_statistics has been provided through this link with fields such as time of delivery, order date, traffic, weather, location, and other information;

· Export, Transform, and Load to Power B.I;

· Inspect and Clean the Data;

· Check the Column if align with the Table Name;

· Order Date -Changed to Date (If an error occurs check the option settings and changed according to the region (Local for Import);

· Timed Order-Rename and changed to Time;

· Time Order Picked- Changed to Time;

· Time Taken extract text after delimiter (min);

· Weather conditions column (remove conditions) — Use Extract text after the delimiter;

· Replace values NaN in the “Weather conditions and the Road traffic columns with “no data.” Remove errors in the “Time ordered and Delivery person age columns.

· Filter rows for delivery_location_latitude (remove outliers);

· Restaurant and delivery Latitude & Longitude changed to Decimal numbers and remove outliers;

· The data for restaurant longitude and latitude have many outliers. Some coordinates lead very far from India, where no cities or towns are located. For visualization, it is better to clean up this data or use the delivery location;

· Type of Vehicle changed to capitalized each word then replace values electric_scooter in Electric Scooter;

· Remove Columns (ID, DeliveryPersonID, VehicleCondition, MultipleDeliveries & Festival)

B.) Modeling:

· Determine how tables related to each other and define and create relationships;

· Create a table then create DimOrder this is for UpdateOrderDate to make an Order date for Weekdays,

Dax Query for Table DimDate:

DimDate = CALENDAR(MIN(delivery_statistics[Order_Date]), MAX(delivery_statistics[Order_Date]))

DimWeekOrder = WEEKDAY(DimDate[Date], 2) 

DimWeekday = FORMAT(DimDate[Date], “DDD”) 

· Connect and manage the relationships for order_date and DimDate

C.) Data Analysis:

· Calculate the median time that is taken for delivery.

MedianDelivery = MEDIAN(delivery_statistics[Time_taken(min)] )

· Calculate the average time between order and pickup of orders;

AvgTimeBetweenOrderAndPickup = AVERAGEX( ‘delivery_statistics’, DATEDIFF(‘delivery_statistics’[Time_Order_picked], ‘delivery_statistics’[Time_Orderd], MINUTE))

· Calculate the longest delivery (min)

LongestDelivery = MAX(delivery_statistics[Delivery_person_Rating])

· Calculate the average delivery person ratings

DeliveryRatings = Average(delivery_statistics[Time_taken(min)])

· (Geographical coordinates)

a.) Compare two geographical coordinates (delivery and restaurant);

b.) Create a map chart that better suits the analysis purpose;

D.) Data Visualizations:

Key Insights:

— After making a dashboard for data-driven decisions, analyzing the delivery time data for the Indian food delivery services,

· The delivery order time varies significantly depending on the mode of transportation, with different types of vehicle transportation being the fastest and road transportation being the slowest. The company should evaluate the cost-benefit of each mode of transportation and optimize its usage accordingly.

· The company’s delivery performance is affected by external factors such as weather conditions and road traffic conditions. The company should have contingency plans in place to handle such situations and ensure minimal impact on delivery times.

Recommendations:

what would you suggest to the delivery company so they can collect better data and get more precise results?

As a Data Analyst, I would recommend that the food delivery services company establish a;

· It should leverage partnerships and collaborations with local logistics providers to improve its delivery network and infrastructure in new markets. This can help the company establish a strong foothold in new markets and compete effectively with local players;

· The company should prioritize improving customer service and communication to ensure customers are aware of their delivery status and any delays. This can help build customer loyalty and satisfaction, which is crucial for success in rating and also in any market.

· Finally, Invest in data analytics and machine learning capabilities to optimize delivery routes, forecast demand, and allocate resources efficiently. This can help the company reduce delivery times and improve overall efficiency.

This dataset was created for this project, subject to the terms and conditions.

All rights reserved_2023©

I would greatly appreciate any corrections and suggestions related to my post that can help me enhance my Data Analysis and Visualization knowledge. I am glad you found my post useful, and I invite you to like or clap it. Please follow me for more updates in the future.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and look over my repository post here:
medium.com/@toledo.alejandro1995

DSA Bottled Drinking Water, Inc. Sales Data 2020–2021

Microsoft Excel got me hooked to learn more and deepen my knowledge and skills. I joined the FREE TRAINING with a Certificate of Completion from DataSense Analytics.

To receive a certificate need to Watch, listen, Apply, and lastly submit an interactive dashboard to fulfil the objectives given below:

Sir Abraham Avila Jr. was our trainer that day he educate us on whats importance of Microsoft Excel using a Pivot Table, He also teach some techniques of Exploratory Data Analysis with Pivot Table in Action. He gave an example of data on how Exploratory Data Analysis works.

1. ) Why Microsoft Excel is important to learn, the reason why they have 4 GOOD REASON:

• Excel is not just for making tables;
• Excel helps you get stuff done;
• Excel is a transferrable skill that any hiring manager understand is critical. That’s the beauty of knowing such a universal computer program;
• It gives you options that will make you better at your job.

1. What is Pivot Table? is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

2.) Benefits of Excel Pivot Tables & Starting Checklist

After the fundamental and solid discussions about Data Analysis with Pivot Table and Exploratory Data Analysis, I applied what I’ve learned so I start the;

Objectives:

a.) Data Entry — data from source documents, compiling, verifying the accuracy, and sorting pieces of information, reviewing inaccuracy or errors of store data;

b.) Data Preparation- the process of data cleaning, Extract, Transform and Load, Transformation( merge, combine, split, extract and assign proper data types) and Data wrangling.

I used some vloolup, index match, or xlookup functions to assign proper data details;

c.) Data Model- communicate connections between data points and structures such as defining relations and cardinality;

d.) Data Analysis- refers to information and organization of data to draw conclusions, make predictions, and drive informed decisions such as Data Aggregation, Descriptive Statistics, and Exploratory Data Analysis;

e.) Data Visualization- graphical representation of information such as a chart, graphs, visuals, dashboards, and story-telling with data.

I used a simple dashboard to make it Interactive and Easy to understand what the data is story-telling about.

I persistently learned and sharpened my skills and knowledge in data analysis, set out information, and data organisation to draw conclusions, make predictions, and drive informed decisions.

I would love and am excited to connect about the opportunity to contribute my skills and experience to your team. I believe my background in skills and experience makes me a strong candidate for the role.

I highly appreciate any corrections and suggestions relevant to my postto enhance/strengthen my knowledge in Data Analysis, Data Viz.
Glad you find this useful, Kindly like/clap my post.
Follow for more incoming updates.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and Check my repository post here:
medium.com/@toledo.alejandro

“Data Analysis Regarding Yellevate’s Client Disputes”

Preface:

Past few years, Yellevate has been struggling with client disputes. Yellevate defines disputes as clients expressing dissatisfaction with the company’s services and refusing to pay for them.

Problem/Issue:

Yellevate is losing money because clients refuse to pay for the services that they have done.

Yellevate is a company that offers marketing services in many countries globally — while this seems like a high-yielding and profitable business, the company still suffers losses due to unpaid services by some clients coming from different countries.

Objectives:

a.) Identify the business problems and develop objectives that can solve with data analysis;

b.) set data analysis goals according to business objectives;

c.) conduct data analysis by loading a .csv file in SQL to perform the necessary data cleaning;

d.) analyze data using Power B.I;

e.) visualize processed data in a manner that visuals will help answer any of the questions;

f.) generate insights from analysis to provide recommendations on probable strategies to deal with disputes effectively.

Scenario/Task Description:

1.) Ask

2.) Prepare

3.) Process

4.) Analyze

5.) Share

6.) Recommendation

Ask

This has been a huge financial burden for the company:

Statistically, nearly 20% of the disputes raised against Yellevate resulted in a payment opt-out. This has led to an approximate 5% annual loss of revenue (in USD).

Using the data gathered.

Executives at the company decided to accomplish the following information to identify the circumstances around the dispute problem.

1. The processing time in which invoices are settled (average # of days rounded to a whole number.

2. The processing time for the company to settle disputes (average # of days rounded to a whole number.

3. Percentage of disputes received by the company that was lost (within two decimal places.

4. Percentage of revenue lost from disputes (within two decimal places.

5. The country where the company reached the highest losses from lost disputes (in USD).

Prepare

Metrics to measure- This project includes a provided link in order to Locate Data.

Sources:

Excel Existing File.csv: Yellevate Invoices Data has been provided through this link and the data dictionary has been provided through this link.

Measures to Protect Data — This dataset was created for this project, subject to the terms and conditions.

Process

• Using PostgreSQL functions to find incorrectly entered data;
• to check for extra spaces
• Removing repeated entries
• Checking as much as possible for bias in the data

PostgreSQL: Yellevate Invoices Data to extract, transform, and load

Using PgAdmin4 to Create a Database from PostgreSQL and create the table Yellevate_invoices.

see the picture below:

After creating a database from PgAdmin4 from PostgreSQL, next is importing data from the .csv file, Click the Icon of the table that is newly created then right-click to import/export .csv data. This Yellevate Invoices.csv link is a Reference to Importing data.

— Refresh;

— Clean Data & Update;

— From columns to disputed, disputed_lost, days_late the 1 & 0 can’t exemplify what it hints at. Data analysis will add a new column to represent what ‘1’ and ‘0’ signified. Also added a column from the year extract;

_code

See the picture below:

This Yellevate_invoices Data’s Insights link is a Reference for updates.

Clean #1 & update data:

Disputed — “1” means the customer disputed the invoice; “0” means they did not.

Clean #2 & update data:

Disputed_lost -”1" means Yellevate lost the dispute and the dispute was resolved in favour of the customer, and the customer does not have to pay the invoice; “0” means the customer did not win the dispute, and they are legally required to pay the full invoice amount, it either means that Yellevate won the dispute, or there was no dispute in the first place.

Clean #3 & update data:

Days_late — Number of days from when the invoice was due to when it was settled, 0 when DueDate > SettledData, and thus the invoice was settled on time.

Additional:

— Extract Year from Settled_date

From PostgreSQL, Import common data sources into Power B.I Desktop

See the picture below:

Analyze

• Perform calculations metrics:

PostgreSQL:

From Power B.I Desktop:

Total Invoice Amount = CALCULATE(SUM(‘public yellevate_invoices’[invoice_amount_usd]))

Total_Revenue_Lost = CALCULATE(SUM(‘public yellevate_invoices’[invoice_amount_usd]), FILTER(‘public yellevate_invoices’,’public yellevate_invoices’[revenue_lost] = “Lost”))

SettleDisputes = CALCULATE(AVERAGE(‘public yellevate_invoices’[days_settled]), FILTER(ALL(‘public yellevate_invoices’[country]), [disputed] = 1))

% of Revenue Lost = DIVIDE([Total_Revenue_Lost], [Total Invoice Amount], 0)

% Disputed Received =DIVIDE(CALCULATE(COUNT(‘public yellevate_invoices’[invoice_amount_usd]), ‘public yellevate_invoices’[disputed_received] = “Disputed”),COUNT(‘public yellevate_invoices’[invoice_amount_usd]))

% Disputed Lost =DIVIDE(CALCULATE(SUM(‘public yellevate_invoices’[disputed]), ‘public yellevate_invoices’[revenue_lost]= “Lost”), SUM(‘public yellevate_invoices’[disputed]))

Invoice & Revenue Sub Title = FORMAT( [Total Invoice Amount] , “$#,##0 M” ) & “ “ & FORMAT( [% of Revenue Lost] , “[Red] +0.0%; -0.0% “ ) & “ ▼ “

• Combine data from multiple sources

CSV to PostgreSQL, Export-Transform-Load — Power B.I (Common Data Sources) Now if want to update the column and row data you may use PostgreSQL to easily update Tables.

Share:

Done to dive into the problem.

Imported Yelevate data into PostgreSQL, Now processed it exported it into Power B.I and made a dashboard with all the relevant information.

Key Insights:

• Yellevate filed a dispute to avoid paying their invoices during the pandemic, as they were adjusting to a skeletal workforce which caused delays in attending to and transmitting the necessary information.
• Late payment of invoices can result in disputes that can harm business relationships and jeopardize a company’s survival.
• On average, it takes 26 days to settle invoices, with Russia having the longest processing time of 29 days, while the year 2021 had the shortest average settled time of 25 days.
• In settled disputes, the company takes an average of 3 days to provide compensation or a grace period to clients, but in Russia, the average is 5 days.
• The company has lost 17.69% of the total disputes received, which amounts to 101 out of 571 disputes, and has lost 4.67% of the total revenue, equal to $690,167 out of $14,770,318.
• The highest amount of revenue lost was in France, with a total of $526,264.

Recommendations:

— For B2b-business to business make a POS-point of sales to track sales details, sales payment summary, brand sales details, and inventory incoming or outgoing sales.

— Make a target mark date to refuse technical errors in each Area, especially in France Country.

— Hired BPO Agents for customer services provider/promoting.

— Do make some page reviews for customer feedback or dispute survey form whether via emails or direct sources of feedback, by prescribed regulations what has been the nature of disputes.

— Make some compensation and promos for clients’ disputes

— Tracking by company branch area needs some audit ASAP if there are uncommon issues.

This dataset was created for this project, subject to the terms and conditions.

All rights reserved_2023©

I would greatly appreciate any corrections and suggestions related to my post that can help me enhance my Data Analysis and Visualization knowledge. I am glad you found my post useful, and I invite you to like or clap it. Please follow me for more updates in the future.

Let’s connect on LinkedIn:
LinkedIn: https://www.linkedin.com/in/alejandrotoledo001

You may follow and look over my repository post here:
medium.com/@toledo.alejandro1995