KPI 1: Weekday vs.

Weekend Payment Statistics

A. Total Orders: Weekday vs. Weekend

This query aims to di erentiate the number of total orders placed on weekdays versus weekends. It
begins by using a SELECT statement with a CASE expression to classify each order based on the day
of the week. Specifically, it uses the DAYOFWEEK() function to extract the day from the
order_purchase_timestamp, which is converted into a date format using STR_TO_DATE() to match the
expected date format ('%d-%m-%Y'). The DAYOFWEEK() function returns values ranging from 1
(Sunday) to 7 (Saturday). If the extracted day corresponds to any of the days from Monday to Friday
(represented by numbers 2 through 6), the CASE statement labels it as "Weekday." Otherwise, it labels
it as "Weekend." The query then counts the total number of orders (COUNT(order_id)) for each
category and groups the results by Day (either "Weekday" or "Weekend").

B. Average Payment Value: Weekday vs. Weekend

This query calculates the average payment value for orders placed on weekdays and weekends.
Similar to the previous query, it uses a CASE statement to classify orders into "Weekday" and
"Weekend" based on the DAYOFWEEK() function. The STR_TO_DATE() function ensures that the
order_purchase_timestamp is interpreted correctly as a date. The JOIN operation links the
olist_orders_dataset table with the olist_order_payments_dataset table using order_id to access
payment information. It then calculates the average payment value for each category using
AVG(p.payment_value). The average is formatted to two decimal places with the FORMAT() function
and prefixed with "R$" for Brazilian Reais currency representation. Finally, the results are grouped by
the Day to distinguish between weekdays and weekends.

KPI 2: Total Number of Orders with Review Score 5 and Payment Type as Credit Card

This query identifies the total number of orders that received a 5-star review and were paid for using a
credit card. It first performs a JOIN operation between the olist_order_reviews_dataset,
olist_orders_dataset, and olist_order_payments_dataset tables using order_id as the common key.
This allows access to review scores and payment information. The WHERE clause filters the results to
include only those orders that have a review score of 5 and a payment type of "credit_card." The query
then counts the distinct order_ids that meet these criteria to determine the total number of such
orders.

KPI 3: Average Delivery Days for Pet Shop Products

This query calculates the average number of days it takes to deliver products from the "pet_shop"
category. It begins by performing a JOIN between the olist_order_items_dataset,
olist_products_dataset, and olist_orders_dataset tables, using the product_id and order_id keys to
access product categories and order dates. The WHERE clause ensures that only orders with a valid
delivery date are considered and filters for products specifically from the "pet_shop" category. The
DATEDIFF() function calculates the di erence in days between the order_delivered_customer_date
and the order_purchase_timestamp. The average delivery days are then computed using AVG() and
rounded to zero decimal places with ROUND() to present a clear average.

KPI 4: Average Price and Payment Values from Customers of São Paulo
This query calculates the average price of items and the average payment value for orders placed by
customers from São Paulo. It uses two subqueries. The first subquery calculates the average price of
products ordered by São Paulo customers by joining the olist_customers_dataset,
olist_orders_dataset, and olist_order_items_dataset tables. The WHERE clause ensures the selection
of customers specifically from São Paulo (customer_city = 'sao paulo' and customer_state = 'SP'). The
average price is calculated and formatted as Brazilian currency. The second subquery computes the
average payment value using similar conditions and joins with the olist_order_payments_dataset for
payment information. The results of these subqueries are combined to provide both average price and
average payment values.

KPI 5: Relationship Between Shipping Days vs. Review Scores

This query explores the relationship between the time taken to deliver orders and the review scores
given by customers. It starts by joining the olist_order_reviews_dataset with the olist_orders_dataset
using order_id. The DATEDIFF() function calculates the di erence between the delivery date
(order_delivered_customer_date) and the order purchase date (order_purchase_timestamp),
representing the number of shipping days. The average of these shipping days is then computed for
each review score using AVG() and rounded to zero decimal places for a more intuitive representation.
The results are grouped and ordered by review_score, allowing analysis of how shipping times might
impact customer reviews.

KPI 6: Total Orders, Price, Freight Value, and Total Amount

This query provides a summary of financial metrics, including total orders, the total price of items,
total freight charges, and the overall amount. It calculates the total number of orders using
COUNT(order_id) and the total price and freight values using SUM(price) and SUM(freight_value),
respectively, from the olist_order_items_dataset. The values are divided by 1,000,000 and formatted
to show amounts in millions (M) with ROUND() and CONCAT() functions, making the figures more
readable and suitable for high-level analysis.

KPI 7: Most Used Payment Type

This query identifies the most common payment methods used by customers. It retrieves payment
data from the olist_order_payments_dataset and groups it by payment_type to determine the number
of occurrences for each type. Using COUNT(order_id), it calculates the total number of payments for
each payment method, making it possible to identify the most frequently used option.

KPI 8: Yearly Approved Orders

This query provides a year-by-year breakdown of approved orders. It extracts the year from the
order_approved_at timestamp using the YEAR() function and ensures the date is correctly parsed with
STR_TO_DATE() in the format '%d-%m-%Y'. It then counts the total number of orders
(COUNT(order_id)) for each year and groups the results by Year. The ORDER BY clause sorts the
results chronologically.

KPI 9: Top 5 Product Categories with Most 5-Star Reviews

This query lists the top 5 product categories that have received the most 5-star reviews. It joins the
olist_order_items_dataset, olist_order_reviews_dataset, and olist_products_dataset using the
order_id and product_id keys to link reviews to specific products. It then filters the results to include
only reviews with a score of 5. The number of such reviews is counted for each product category using
COUNT(r.review_score), and the results are grouped by product_category_name. Finally, the query
sorts the categories in descending order of review count and limits the output to the top 5.

KPI 10: Top 5 Product Categories with Most 1-Star Reviews

This query is similar to the previous one but focuses on 1-star reviews instead of 5-star reviews. It
identifies the product categories that received the most 1-star reviews by joining the same tables and
filtering for review_score = 1. The results are counted, grouped by product category, sorted in
descending order of review count, and limited to the top 5 categories, helping identify product areas
with potentially lower customer satisfaction.