Meesho HackerRank SQL Interview Question and Answer | Customer Budget

In this video we will discuss an SQL interview question that was asked in meesho's HackerRank online test.

Google SQL Interview Question:

Most Asked Join Based Interview Question:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

DDL:
create table products
(
product_id varchar(20) ,
cost int
);
insert into products values ('P1',200),('P2',300),('P3',500),('P4',800);

create table customer_budget
(
customer_id int,
budget int
);

insert into customer_budget values (100,400),(200,800),(300,1500);

#sql #businessanalyst #meesho #hackerrank

[TheSmartTrendTrader]

Hello Ankit, I tried this way before looking at your solution. almost similar approach though but I liked your approach because it's much cleaner. You are doing a great job!!!

with t1 as (select c.customer_id, c.budget, p.product_id, p.cost as product_cost, c.budget-p.cost as remaining_budget,
sum(p.cost) over (partition by c.customer_id order by p.cost) as rolling_sum
from customer_budget c cross join products p
where c.budget > p.cost)
select t1.customer_id, t1.budget, count(t1.product_id) as no_of_products, STRING_AGG(product_id, ', ') as list_of_products
from t1 where t1.budget > t1.rolling_sum
group by customer_id, budget

[KoushikT]

Thanks Ankit for the question, It was really good. My solution was also the same as yours but with slight change in implementation


with running_cost as
(
select *,
sum(cost) over (rows between unbounded preceding and current row) as running
from products
)

select customer_id,
min(budget) as budget,
count(*) as no_of_products,
string_agg(product_id, ', ') as list_of_products
from customer_budget c
join running_cost p
on p.running<=c.budget
group by customer_id
order by customer_id

[sreejitchakraborty]

Using cross join and rolling sum:


with cte as(
select customer_id, budget, product_id, cost,
sum(cost) over(partition by customer_id order by cost rows between unbounded preceding and current row) as rolling_sum from
(
select * from customer_budget cross join products
)n
)
select customer_id, budget, count(*) as no_of_prods, group_concat(product_id)
from
(
select * from cte where rolling_sum<=budget
)
group by customer_id, budget

[francis.joseph]

with cte1 as(select *,
sum(cost) over( order by product_id asc) as running_sum
from products )
select customer_id, array_agg(product_id) as products, budget, count(product_id) as no_of_products
from customer_budget c
join cte1 p
on p.cost < c.budget and p.running_sum < c.budget
group by customer_id, budget
order by customer_id;

[Datapassenger_prashant]

first posting the query without seeing the solution video, , thanks for making me that much capable that, Ican solve such problem statements easily.

Select customer_id, budget, count(*) as Count_of_Products, STRING_AGG( prd, ', ') as List_of_Products from (
Select c.customer_id, c.budget
, case when max(budget) over(partition by customer_id) > sum(p.cost) over(partition by customer_id order by p.cost) then p.product_id end as Prd
from customer_budget c, products p) A
where prd is not null
Group by customer_id, budget

[mananagrawal]

HI ANKIT, MY Solution using cross join below: with cte as(
SELECT *, sum(cost) over (Partition by customer_id order by cost) as running_cost FROM
customer_budget c cross join products p
)
SELECT customer_id, budget, String_agg(product_id, ', ') as list_of_products, COUNT(1) as no_of_products
FROM cte
where running_cost<=budget
GROUP by customer_id, budget

[abhishek_grd]

Ankit, Alongwith that i am requesting you to make a playlist of DataBase design concepts and examples. Like -
1) Online Shopping (one of your example can covers all type of design wrt flipkart, Amazon, etc etc.)

2) Tournament ( one example will cover all like ICC world cup or any other ).

3) Online Streaming ( it will covers all like Prime Video, Netflix, hotstar etc)

4) Supply Chain management ( FedEx, Amazon, UPS etc )

5) Ordering application ( swiggy, Zomato, Domino)

6) Storage Application ( Google drive, one drive ) etc...

Really appreciate it.

[tupaiadhikari]

This is my Solution before looking through this Video, but took inspiration from your videos I watched earlier
```
SELECT
customer_id,
cust_budget,
count(*) AS numberofItems,
GROUP_CONCAT(product_id SEPARATOR ', ') AS listOfProducts
FROM
(
SELECT
*
FROM
(
SELECT
*,
SUM(priceWithinBudget) OVER (PARTITION BY customer_id ORDER BY priceWithinBudget) AS totalPurchaseAmount
FROM (

SELECT
a.customer_id,
a.budget AS cust_budget,
b.product_id,
b.cost AS priceWithinBudget
FROM
customer_budget AS a
,
products AS b
WHERE
a.budget>b.cost
ORDER BY
customer_id
) AS c
) AS d
WHERE

AS
e
GROUP BY
customer_id,
cust_budget;

[Mysingh]

Thanks Ankit bhai... You are Rockstar of SQL

[pushpanthkumar]

Thank you for the video. Could you please consider making video on how to Crack amazon data engineer interview please.

[ashpoghosh]

In My SQL

with running_total as
(select product_id, cost, sum(cost) over(order by product_id) as run from products)

select c.customer_id, c.budget, count(product_id) as no_of_products, group_concat(product_id) as list_products from running_total r, customer_budget c
where c.budget>r.run group by c.customer_id, c.budget

[florincopaci]

This is a very good question like all of you question ! please come with more like this!

[aaravkumarsingh]

with cte as(
select *, sum(cost) over(order by cost rows between unbounded preceding and current row) as continue_sum
from products)
select customer_id, budget, count(*) as no_of_products, group_concat(c.product_id order by c.product_id)
from customer_budget cb left join cte c on cb.budget> c.continue_sum
group by customer_id, budget;

[gstinge]

Thanks for making this video' - on joining table when two tables have a different column name

[amritbhowmick]

is there any difference between running_cost < budget and budget > running_cost while using on join condition?

[Buzzingfact]

Here i go:

with temp as (

select *, budget-cost as diff
from products2, customer_budget
where cost<budget
)
select customer_id, budget, count(*), STRING_AGG(product_id, ', ') as products
from temp
where diff>= cost
group by customer_id, budget
order by customer_id

[imranidrisi]

select company_id from company_users where language in ('English', 'German') group by company_id having user_id)>=2

[skrishnakishore]

with cte as (select *, sum(cost)over(order by cost)as summ, by cost) as grp from products
)
,
cte2 as (select c.*, grp, rank()over(partition by customer_id order by length(grp) desc)as rn, count(grp)over(partition by customer_id ) as numberofproducts from
cte
join customer_budget c
on budget>=summ
order by customer_id
)
select * from cte2
where rn=1

[rajunaik]

with cte1 as(
select product_id, cost, sum(cost) over(order by product_id asc, cost asc rows between unbounded preceding and 0 preceding)as running_total
from products
), cte2 as(
select * from cte1 join customer_budget on cost<budget
where budget>=running_total --order by customer_id, running_total
)
select customer_id, max(budget) as budget, count(product_id) as no_of_products, STRING_AGG(product_id, ', ') as list_of_products
from cte2 group by customer_id

[manishsinghpatel]

Very useful video💯 . Thanks for sharing 🙌