Big Data Mock Interview

[@saurabhshrigadi]

Perform the join, group by product put the rank window function partition by day order by quantity . Where rank < 6

[@abhisheknigam3768]

Window function is favourite question of interviewer. . Find duplicates, find third highest salary is also similar question of window function.

[@shiprahota6497]

True

[@Bnilesh]

Excellent 👏 👏

[@218Flow]

6:00 Limit should work if you order by the aggregation DESC

[@shiprahota6497]

Thanks for the video

[@anshusharaf2019]

can we solve using max function and group by on day extract from date column??

[@darsikavya]

Is there any chance I can be a interviewee because I am also preparing for Data Engineer roles. This will help me

[@iamworstgamer]

no introduction nothing just started with questions

[@hangdoan9077]

First, i will join 3 tables to get orderdate, total_amount, product_name/product_id, then group by (date(orderdate) as order_date_new, productid, productname) and sum of total_amount), then apply window function row_number because it has consequences with partition by order_date_new meaning sorting in each order_date_new with total_amount desc then just use filter row_number

[@orlandocastellanos9263]

The girl got the job?

[@rahidzeynal]

with cte as
(
select oi.product_id, trunc(order_date) as trc_day, sum(oi.quantity) as sm,
from orders o
inner join orders_item oi on oi.order_id = o.order_id
group by oi.product_id, trunc(order_date)
),
cte2 as (
select p.product_name, trc_day, sm, ROW_NUMBER() over (partition by p.product_name, trc_day order by sm desc) as rn
from cte as c
inner join products p on p.product_id = c.product_id
)
select c2.product_name, c2.trc_day, c2.sm
from cte2 c2
where c2.rn