Amazon Brazil wants to identify trends and customer behaviours that could be leveraged in the

Indian market, given the similarities between Brazil and India—such as large populations and diverse
consumer bases—there is an opportunity to replicate success in India. The main objective is to
analyse trends, customer behaviours, and preferences that could be leveraged in the Indian market
which will help Amazon India make informed decisions, enhance customer experience, and seize new
opportunities.

This project uses multiple tables: Customers, Orders, Order Items, Product, Sellers, and Payments.
Through SQL queries, various business-critical questions and actionable insights have been shared.

_________________________________________________________________________________

ANALYSIS – I
Question 1

Problem Statement:

To simplify its financial reports, Amazon India needs to standardise payment values. Round the
average payment values to integer (no decimal) for each payment type and display the results sorted
in ascending order. Output: payment_type, rounded_avg_payment

Approach:

1. Identifying Relevant Tables and Columns:

o Table: payments

o Columns: payment_type, payment_value

2. Calculating Average Payment Value:

o Used the AVG() aggregate function on payment_value

o Grouped the results by payment_type to get the average for each type

3. Rounding the Averages:

o Used the ROUND() function to round the average payment values to the nearest
integer.

4. Ordering the Results:

o Ordered the final results in ascending order based on the rounded average payment.

SQL QUERY:

SELECT payment_type, ROUND(AVG(payment_value)) AS rounded_avg_payment

FROM amazon_brazil."Payments"

WHERE payment_value > 0

GROUP BY payment_type

ORDER BY rounded_avg_payment;
OUTPUT:

Recommendations:
1. Focus on Popular Payment Methods:

o Payment methods like Credit Card and Boleto should be optimised, as their average
payment values are higher.

o Introducing targeted promotions for customers using these methods can lead to
higher revenue.

2. Improve Use of Underperforming Methods:

o For payment methods like Debit Card and Voucher, giving promotional discounts or
improving transaction processes can be helpful to retain users while increasing usage
rate and amount as their average payment values are lower.
Question 2

Problem Statement:

To refine its payment strategy, Amazon India wants to know the distribution of orders by
payment type. Calculate the percentage of total orders for each payment type, rounded to
one decimal place, and display them in descending order. Output: payment_type,
percentage_orders

Approach:

1. Identifying Relevant Tables and Columns:

o Table: payments & Columns: payment_type, order_id

2. Calculating Total Number of Orders:

o Used COUNT() on order_id

3. Calculating Orders per Payment Type:

o Used COUNT() on payment_type

4. Calculating Percentage of Orders and Rounding the result:

o Divided the count of orders per payment type by the total number of orders,
multiplied by 100, and rounded to one decimal place.

5. Sorting Results:
o Ordered the results in descending order of percentage to highlight the most
popular payment methods.

SQL QUERY:
SELECT payment_type,

ROUND((COUNT(payment_type)*100.0)/(SELECT COUNT(DISTINCT order_id) FROM

amazon_brazil."Payments"), 1) AS percentage_orders

FROM amazon_brazil."Payments"

WHERE payment_value > 0

GROUP BY payment_type

ORDER BY percentage_orders DESC;

Output:

Recommendations:

1. Maintain efficiency of Popular Payment Methods:

o Since Credit Card is very popular, this payment method needs to be highly reliable,
secure, and easy to use.

o Consider cashback/reward or loyalty programs for Credit Card users to incentivize

more purchases and to increase average order value.

2. Improve Less Popular Payment Methods:

o Explore ways to make Boleto, Voucher, and Debit Card more attractive by offering
discounts or simplifying the payment process.

o Further analysis needs to be done to know reason behind low usage of certain
methods and address any underlying cause.
Question 3

Problem Statement:

Amazon India seeks to create targeted promotions for products within specific price
ranges. Identify all products priced between 100 and 500 BRL that contain the word 'Smart'
in their name. Display these products, sorted by price in descending order.

o Output: product_id, price

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Product, Order Items

b. Columns: product_id, price

2. Used the ROUND() function to round the price to the nearest integer.
3. Used INNER JOIN() to join Product Table and Order Items Table on product_id as common
column
4. Used WHERE() to put required conditions
5. Used Order BY clause to order price from highest to lowest

SQL QUERY:
SELECT pd.product_id, ROUND([Link], 0) AS price

FROM amazon_brazil."Product" pd

JOIN amazon_brazil."Order_Items" oi ON pd.product_id = oi.product_id

WHERE [Link] BETWEEN '100' AND '500' AND pd.product_category_name LIKE '%smart%'

ORDER BY [Link] DESC;

OUTPUT:
Question 4

Problem Statement:

To identify seasonal sales patterns, Amazon India needs to focus on the most successful
months. Determine the top 3 months with the highest total sales value, rounded to the nearest
integer. Output: month, total_sales

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Payments, Orders

b. Columns: payment_value, order_purchase_timestamp, order_id

2. Used ROUND() to round payment value to nearest integer and extracted month name from
timestamp using TO_CHAR()
3. Used JOIN() to join both tables based on order_id
4. Used GROUP() to get total_sales based on each month
5. Used ORDER() to order total_sales in Descending order to find Top 3 successful month using
LIMIT.

SQL Query:
SELECT ROUND(SUM(pt.payment_value)) AS total_sales, To_CHAR(o.order_purchase_timestamp,
'Month') as months

FROM amazon_brazil."Payments" pt

JOIN amazon_brazil."Orders" o ON pt.order_id = o.order_id

GROUP BY months

ORDER BY total_sales DESC

LIMIT 3;
OUTPUT:

Question 5

Problem Statement:

Amazon India is interested in product categories with significant price variations. Find categories
where the difference between the maximum and minimum product prices is greater than 500 BRL.
Output: product_category_name, price_difference

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Product, Order Items

b. Columns: product_category_name, price, product_id

2. Used ROUND() to round difference of Max and Min of price to nearest integer and extracted
month name from timestamp using TO_CHAR()
3. Used JOIN() to join both tables based on product_id
4. Used GROUP() to group price by product_category_name
5. Used HAVING() to put required conditions

SQL Query:

SELECT pd.product_category_name, MAX([Link]) - MIN([Link]) AS price_difference

FROM amazon_brazil."Product" pd

JOIN amazon_brazil."Order_Items" oi ON pd.product_id = oi.product_id

GROUP BY pd.product_category_name

HAVING MAX([Link]) - MIN([Link]) > 500

ORDER BY price_difference DESC;

OUTPUT:

Question 6

Problem Statement:

6. To enhance the customer experience, Amazon India wants to find which payment types have the
most consistent transaction amounts. Identify the payment types with the least variance in
transaction amounts, sorting by the smallest standard deviation first. Output: payment_type,
std_deviation

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Payments

b. Columns: payment_type,

2. Used STDDEV() to find variance in payment_value

3. Used GROUP BY() to get payment_value for each payment_type
4. Used ORDER BY() to order std_deviation in descending order

SQL Query:

SELECT payment_type, STDDEV(payment_value) AS std_deviation

FROM amazon_brazil."Payments"

WHERE payment_value > 0

GROUP BY payment_type

ORDER BY std_deviation;
OUTPUT:

Question 7

Problem Statement:

Amazon India wants to identify products that may have incomplete name in order to fix it from their
end. Retrieve the list of products where the product category name is missing or contains only a
single character. Output: product_id, product_category_name

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Product

b. Columns: product_id, product_category_name

2. Used WHERE() to to filter under given conditions

3. Used ORDER BY() to order product_category_name in descending order

SQL Query:

SELECT product_id, product_category_name

FROM amazon_brazil."Product"

WHERE product_category_name IS NULL OR product_category_name LIKE '_'

ORDER BY product_category_name;
OUTPUT:

Recommendations:
1. Fix incomplete names
o Adding missing names in product category can give clarity about product to users
which will be helpful in increasing sales.

ANALYSIS: II
Question 1

Problem Statement:

Amazon India wants to understand which payment types are most popular across different order
value segments (e.g., low, medium, high). Segment order values into three ranges: orders less than
200 BRL, between 200 and 1000 BRL, and over 1000 BRL. Calculate the count of each payment type
within these ranges and display the results in descending order of count.
Output: order_value_segment, payment_type, count

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Payments

b. Columns: payment_value, payment_type,

2. Used CASE WHEN() statements to segment customers based on given payment_value

3. Used COUNT() on payment type to get count of each payment type on given range
4. Used GROUP BY() to get payment_value for each payment_type and segment
5. Used ORDER BY() to order payment_count in ascending order
SQL Query:

SELECT

CASE

WHEN payment_value < 200 THEN 'LOW'

WHEN payment_value BETWEEN 200 AND 1000 THEN 'MEDIUM'

ELSE 'HIGH'

END AS order_value_segment,

payment_type, COUNT(payment_type) AS payment_count

FROM amazon_brazil."Payments"

GROUP BY payment_type, order_value_segment

ORDER BY payment_count DESC;

OUTPUT:
Question 2

Problem Statement:

Amazon India wants to analyse the price range and average price for each product
category. Calculate the minimum, maximum, and average price for each category, and list them in
descending order by the average price.

Output: product_category_name, min_price, max_price, avg_price

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Product, Order_Items

b. Columns: product_category_name, price

2. Calculating MIN, MAX and Average Payment Value:

o Used the MIN(), MAX(), AVG() aggregate function on price

o Grouped the results by product_category _name to get rounded value for each
function

3. Used JOIN() to join Product table with Order_Items table on product_id

4. Used ORDER BY() to order price in ascending order

SQL Query:

SELECT pd.product_category_name, ROUND(MIN([Link]), 2) AS min_price, ROUND(MAX([Link]),

2) AS max_price,

ROUND(AVG([Link]), 2) AS avg_price

FROM amazon_brazil."Product" pd

JOIN amazon_brazil."Order_Items" oi ON pd.product_id = oi.product_id

GROUP BY pd.product_category_name

ORDER BY avg_price DESC;

OUTPUT:

Question 3

Problem Statement:

Amazon India wants to identify the customers who have placed multiple orders over time. Find all
customers with more than one order, and display their customer unique IDs along with the total
number of orders they have placed.

Output: customer_unique_id, total_orders

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Payments

b. Columns: payment_value, payment_type,

2. Used COUNT() on order_id to get number of total orders

3. Used GROUP BY() with HAVING to group customers based on their unique id and more than 1
orders
4. Used ORDER BY() to order total orders in ascending order
SQL Query:

SELECT cs.customer_unique_id, COUNT(o.order_id) AS total_orders

FROM amazon_brazil."Orders" o

JOIN amazon_brazil."Customers" cs ON o.customer_id = cs.customer_id

GROUP BY cs.customer_unique_id

HAVING COUNT(o.order_id) > 1

ORDER BY total_orders DESC;

OUTPUT:
Question 4

Problem Statement:

Amazon India wants to categorize customers into different types ('New – order qty. = 1' ; 'Returning'
–order qty. 2 to 4; 'Loyal' – order qty. >4) based on their purchase history. Use a temporary table to
define these categories and join it with the customers table to update and display the customer
types. Output: customer_id, customer_type

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders, Customers

b. Columns: order_id, customer_id

2. Used CASE WHEN() statements to segment customers based on number of orders placed by
each customer
o Used COUNT() on order_id to count number of orders
o Used GROUP BY() TO group number of orders by customer_id
3. Used JOIN() on ct and customers table to find required values

SQL Query:

WITH ct AS (

SELECT o.customer_id,

CASE

WHEN COUNT(order_id) = 1 THEN 'NEW'

WHEN COUNT(order_id) BETWEEN 2 AND 4 THEN 'RETURNING'

WHEN COUNT(order_id) > 4 THEN 'LOYAL'

ELSE 'NO ORDER'

END AS customer_type

FROM amazon_brazil."Orders" o

GROUP BY customer_id

SELECT cs.customer_id, ct.customer_type

FROM amazon_brazil."Customers" cs

LEFT JOIN ct on cs.customer_id = ct.customer_id

OUTPUT:

Question 5

Problem Statement:

Amazon India wants to know which product categories generate the most revenue. Use joins
between the tables to calculate the total revenue for each product category. Display the top 5
categories. Output: product_category_name, total_revenue

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Product, Order_Items

b. Columns: product_category_name, price, product_id

2. Using aggregate function

o Used SUM() to find total amount of product ordered

o Used GROUP Y() TO group it by product_category_name
3. Used INNER JOIN() on product_id to join Product table with Order_Items table
4. Used ORDER BY() to order total_revenue in descending order
5. Used LIMIT clause to find Top 5 categories
SQL Query:

SELECT pd.product_category_name, SUM([Link]) AS total_revenue

FROM amazon_brazil."Product" pd

JOIN amazon_brazil."Order_Items" oi ON pd.product_id = oi.product_id

GROUP BY pd.product_category_name

ORDER BY total_revenue DESC

LIMIT 5;

OUTPUT:
 ANALYSIS: III

Question 1

Problem Statement:

The marketing team wants to compare the total sales between different seasons. Use a subquery to
calculate total sales for each season (Spring, Summer, Autumn, Winter) based on order purchase
dates, and display the results. Spring is in the months of March, April and May. Summer is from June
to August and Autumn is between September and November and rest months are Winter.
Output: season, total_sales

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders, Payments

b. Columns: order_purchase_timestamp, payment_value, order_id

2. Used CASE WHEN() statements to segment customers based on months

o Used EXTRACT() to get month values and segmented it into season of year
o Used SUM() on payment_value to get value of total sales
3. Used INNER JOIN() ON ORDER_ID to join Orders table with Payments table
4. Used GROUP BY() on season to get sales values on each season

SQL Query:

SELECT

CASE

WHEN EXTRACT(MONTH FROM order_purchase_timestamp) IN (3, 4, 5) THEN 'Spring'

WHEN EXTRACT(MONTH FROM order_purchase_timestamp) IN (6, 7, 8) THEN 'Summer'

WHEN EXTRACT(MONTH FROM order_purchase_timestamp) IN (9, 10, 11) THEN 'Autumn'

ELSE 'Winter'

END AS season,

SUM(pt.payment_value) AS total_sales

FROM amazon_brazil."Orders" O

JOIN amazon_brazil."Payments" pt ON o.order_id = pt.order_id

GROUP BY season;
OUTPUT:

Question 2

Problem Statement:

The inventory team is interested in identifying products that have sales volumes above the overall
average. Write a query that uses a subquery to filter products with a total quantity sold above the
average quantity. Output: product_id, total_quantity_sold

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Order_Items

b. Columns: product id, order_id

2. Used CTE()
o Used COUNT() on order_item_id to calculate total_quantity_sold
o Used GROUP BY() to group it by product_id
3. Used Subquery in WHERE clause to filter products based on given conditions
4. Used GROUP BY() to get payment_value for each payment_type and segment
5. Used ORDER BY() to order total_quantity_sold in descending order
SQL Query:

WITH coi AS (

SELECT product_id, COUNT(order_item_id) AS total_quantity_sold

FROM amazon_brazil."Order_Items"

GROUP BY product_id

SELECT product_id, total_quantity_sold

FROM coi

WHERE total_quantity_sold > (

SELECT AVG(total_quantity_sold) FROM coi

ORDER BY total_quantity_sold DESC;

OUTPUT:
Question 3

Problem Statement:

To understand seasonal sales patterns, the finance team is analysing the monthly revenue trends
over the past year (year 2018). Run a query to calculate total revenue generated each month and
identify periods of peak and low sales. Export the data to Excel and create a graph to visually
represent revenue changes across the months. Output: month, total_revenue

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders, Payments

b. Columns: payment_value, order_purchase_timestamp

2. Used LEFT JOIN() to get relevant columns

o Used TO_CHAR() and DATE_TRUNC() in timestamp to calculate month values
o Used EXTRACT() in timestamp to fix year as 2018
o Used GROUP BY() to group above data by Months
o Usesd OORDER BY() to order above data by Months
3. Used SUM() to get sum of payment_value and grouped it by months

SQL Query:

SELECT TO_CHAR(DATE_TRUNC('MONTH', o.order_purchase_timestamp), 'YYYY-MM') AS month,

SUM(pt.payment_value) AS total_revenue

FROM amazon_brazil."Orders" o

JOIN amazon_brazil."Payments" pt ON o.order_id = pt.order_id

WHERE EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2018

GROUP BY month

ORDER BY month;
OUTPUT:
Question 4

Problem Statement:

A loyalty program is being designed for Amazon India. Create a segmentation based on purchase
frequency: ‘Occasional’ for customers with 1-2 orders, ‘Regular’ for 3-5 orders, and ‘Loyal’ for more
than 5 orders. Use a CTE to classify customers and their count and generate a chart in Excel to show
the proportion of each segment. Output: customer_type, count

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders

b. Columns: order_id, customer_id

2. Used CASE WHEN() statements to segment customers based on number of orders

3. Used COUNT() on order_id to calculate total orders for each customer
4. Used GROUP BY() to group customer_type based on customer_id

SQL Query:

WITH cust_det AS(

SELECT

CASE

WHEN COUNT(order_id) BETWEEN 1 AND 2 THEN 'Occasional'

WHEN COUNT(order_id) BETWEEN 3 AND 5 THEN 'Regular'

ELSE 'Loyal'

END AS customer_type,

COUNT(order_id) AS Order_Count

FROM amazon_brazil."Orders"

GROUP BY customer_id

SELECT customer_type, COUNT(*) AS count

FROM cust_det

GROUP BY customer_type

ORDER BY count DESC;

OUTPUT:

Question 5

Problem Statement:

Amazon wants to identify high-value customers to target for an exclusive rewards program. You are
required to rank customers based on their average order value (avg_order_value) to find the top 20
customers. Output: customer_id, avg_order_value, and customer_rank

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders, Payments

b. Columns: customer_id, payment_value

2. Used CTE() to calculate required columns

3. Used the AVG() aggregate function on payment_value. Grouped the result by customer_id to
get payment value for each type
4. Used the ROUND() function to round the average payment values to two decimal place.
5. Used GROUP BY() to group avg_order_value based on customer_id
6. Used RANK() FROM WINDOW FUNCTION to find Top 20 customers using LIMIT clause

7. Used ORDER BY() to order the final results in descending order based on the rounded average
payment.

8. Used LIMIT to get Top 20 customers.

SQL Query:
WITH Top_20 AS(

SELECT o.customer_id, ROUND(AVG(pt.payment_value),2) AS avg_order_value

FROM amazon_brazil."Orders" o

JOIN amazon_brazil."Payments" pt ON o.order_id = pt.order_id

GROUP BY o.customer_id

SELECT customer_id, avg_order_value,

RANK() OVER(ORDER BY avg_order_value DESC) AS "customer_rank"

FROM Top_20

ORDER BY avg_order_value DESC;

LIMIT 20;

OUTPUT:
Question 6

Problem Statement:

Amazon wants to analyze sales growth trends for its key products over their lifecycle. Calculate
monthly cumulative sales for each product from the date of its first sale. Use a recursive CTE to
compute the cumulative sales (total_sales) for each product month by month. Output: product_id,
sale_month, and total_sales

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Orders, Order_Items

b. Columns: product_id, order_purchase_timestamp, order_id

2. Used CTE to calculate relevant columns

o Used To_CHAR() and DATE_TRUNC to convert given timestamp to month value
o Used SUM() on price to get monthly sales
o Used LEFT JOIN() to join Orders table with Order Items table on order id
o Used GROUP BY() to get values based on product id and sale month
3. Used SUM() through window function on product id and ordered by sale month to get total
sales
4. Used ORDER BY() to order it by product_id and sale month

SQL Query:

WITH growth_trends AS(

SELECT oi.product_id,

TO_CHAR(DATE_TRUNC('month', o.order_purchase_timestamp), 'YYYY-MM') AS

sale_month,

SUM([Link]) AS monthly_sales

FROM amazon_brazil."Orders" o

JOIN amazon_brazil."Order_Items" oi ON o.order_id = oi.order_id

GROUP BY oi.product_id, sale_month

SELECT product_id, sale_month, monthly_sales,

SUM(monthly_sales) OVER(PARTITION BY product_id ORDER BY sale_month) AS total_sales

FROM growth_trends

ORDER BY product_id, sale_month;

OUTPUT:

Question 7

Problem Statement:

To understand how different payment methods affect monthly sales growth, Amazon wants to
compute the total sales for each payment method and calculate the month-over-month growth rate
for the past year (year 2018). Write query to first calculate total monthly sales for each payment
method, then compute the percentage change from the previous month.

Output: payment_type, sale_month, monthly_total, monthly_change.

Approach:

1. Identifying relevant Tables and Columns:

a. Table: Payments, Orders

b. Columns: payment_type, payment_value, order_purchase_timestamp, order_id

2. Used CTE to get relevant columns

o Used TO_CHAR() and DATE_TRUNC() in timestamp to calculate month values
o Used SUM() on payment_value to get monthly_total
o Used LEFT JOIN() on Orders table to join with Payments table
o Used DATE_PART() in timestamp to fix year as 2018
o Used GROUP BY() to group above data by payment type and month
3. Used LAG() window function and ROUND() to get month on month change
4. Used ORDER BY() to order above data by payment type, and sale month
SQL Query:

WITH monthly_sales AS(

SELECT pt.payment_type,

TO_CHAR(DATE_TRUNC('month', o.order_purchase_timestamp), 'YYYY-MM') AS sale_month,

SUM(payment_value) AS monthly_total

FROM amazon_brazil."Payments" pt

JOIN amazon_brazil."Orders" o ON pt.order_id = o.order_id

WHERE DATE_PART('Year', o.order_purchase_timestamp) = 2018 AND pt.payment_value > 0

GROUP BY pt.payment_type, sale_month

SELECT payment_type, sale_month, monthly_total,

ROUND(((monthly_total - LAG(monthly_total) OVER(PARTITION BY payment_type ORDER BY

sale_month))*100/ (LAG(monthly_total) OVER(PARTITION BY payment_type ORDER BY
sale_month))), 2) || '%' AS monthly_change

FROM monthly_sales

ORDER BY payment_type, sale_month;

OUTPUT: