After a long follow up with Amazon HR team today I took my telephonic interview round 1 for a Business Intelligence role, After my little introduction we jumped into sql evaluation....

1. Types of Joins
2. Diff B/w Self join and Inner Join
3. What is Cross Join and what is the use

Then jumped into sql notepad for scenario based evaluation.
Table1: "Orders"
order_date
order_id
customer_id
book_id
quantity
sales_per_unit

Table2: "Books"
Book_ID
Genre
Publisher

Q1: Monthly Sales by publisher
select publisher, to_date(order_date, yy), to_date(order_date, mm), sum(quantity * sales_per_unit) as tot_per_month
from orders ord join Books bks ON ord.book_id = bks.book_id
group by bks.publisher, to_date(order_date, mm), to_date(order_date, yy)

Q2: Count of new customers in 2021
select count(customer_id) as new_customers from (
select customer_id, order_date, row_number() over (partition by customer_id order by order_date desc) as ord_dt from Orders
)
where ord_dt=1 and to_char(order_date, yyyy)='2021'

Q3: Bestselling Publisher in each Genre
select publisher, genre from (
select publisher, genre, dense_rank() over (partition by publisher, genre order by tot_sold desc) Best_Selling from
(select bks.publisher, ord.book_id, bks.genre, sum(quantity) tot_sold
from
Orders ord join Books bks ON ord.book_id = bks.book_id
)) where best_selling =1

The interview completed and with in 20 Min I got a mail stating rejected, Since Amazon is not going to provide any feed back, I am requesting all the users to tell me where I went wrong, I didn't ran the queries, but thretically the concept will give you the result.