sql doubt

[nikhilboorla]

Ecommerce Data Mart

Transaction -> Transaction_id, buyer_id, seller_id, object_id,Shipping_id, Price, Quantity, site_id,transaction_date, expected_delivery_date, check_out_status
                            leaf_category_id, defect_id

Buyer -> Buyer_id, name, country

Seller -> Seller_id, name, country, segment, standard

Listing -> object_id, seller_id, auction_start_date
               auction_end_date, listing_site_id, leaf_category_id
               quantity

For the sellers from UK who transacted on the second week of december(6 December 2015 to 12 December 2015), find the number of sellers
who have atleast twice the total transaction amount (qty*price) in the following week  

[MagaMaharaju]

Nikipikil

[panipoori]

5 minutes ago, nikhilboorla said:

Ecommerce Data Mart

Transaction -> Transaction_id, buyer_id, seller_id, object_id,Shipping_id, Price, Quantity, site_id,transaction_date, expected_delivery_date, check_out_status
                            leaf_category_id, defect_id

Buyer -> Buyer_id, name, country

Seller -> Seller_id, name, country, segment, standard

Listing -> object_id, seller_id, auction_start_date
               auction_end_date, listing_site_id, leaf_category_id
               quantity

For the sellers from UK who transacted on the second week of december(6 December 2015 to 12 December 2015), find the number of sellers
who have atleast twice the total transaction amount (qty*price) in the following week  

Technical challenge ah in Interview? eh company?

[nikhilboorla]

5 minutes ago, panipoori said:

Technical challenge ah in Interview? eh company?

Latentview

[panipoori]

2 minutes ago, nikhilboorla said:

Latentview

Tables clear gaane vndi kaani question is confusing kncham....

My take

With trans_dec_uk as

(

select s.seller_id,t.transaction_date, sum(t.Qty * Price) trans_amount

from transaction t join seller s

on t.seller_id =s.seller_id

where s.country ='UK'

and t.transaction_date between '12-05-2015' and '12-18-2015'

group by s.seller_id,t.transaction_date

)

This is the subest of the data.. I think you should use self join on this table to achieve the solution

 

 

[BetterThief]

26 minutes ago, nikhilboorla said:

Ecommerce Data Mart

Transaction -> Transaction_id, buyer_id, seller_id, object_id,Shipping_id, Price, Quantity, site_id,transaction_date, expected_delivery_date, check_out_status
                            leaf_category_id, defect_id

Buyer -> Buyer_id, name, country

Seller -> Seller_id, name, country, segment, standard

Listing -> object_id, seller_id, auction_start_date
               auction_end_date, listing_site_id, leaf_category_id
               quantity

For the sellers from UK who transacted on the second week of december(6 December 2015 to 12 December 2015), find the number of sellers
who have atleast twice the total transaction amount (qty*price) in the following week  

Nice

[Sarvapindi]

Easy 

[gunturfriends]

5 hours ago, Sarvapindi said:

Easy 

 

9 hours ago, nikhilboorla said:

Ecommerce Data Mart

Transaction -> Transaction_id, buyer_id, seller_id, object_id,Shipping_id, Price, Quantity, site_id,transaction_date, expected_delivery_date, check_out_status
                            leaf_category_id, defect_id

Buyer -> Buyer_id, name, country

Seller -> Seller_id, name, country, segment, standard

Listing -> object_id, seller_id, auction_start_date
               auction_end_date, listing_site_id, leaf_category_id
               quantity

For the sellers from UK who transacted on the second week of december(6 December 2015 to 12 December 2015), find the number of sellers
who have atleast twice the total transaction amount (qty*price) in the following week  

SELECT COUNT(1) FROM (
SELECT tr.seller_id
SUM(CASE WHEN transaction_date between '2015-12-06' AND '2015-12-10' THEN tr.Price*tr.Quantity ELSE 0 END) AS this_week_amount,
SUM(CASE WHEN transaction_date between '2015-12-13' AND '2015-12-19' THEN tr.Price*tr.Quantity ELSE 0 END) AS following_week_amount
FROM Transactions tr JOIN Seller s ON tr.seller_id=s.Seller_id WHERE s.country='UK' AND tr.transaction_date BETWEEN '2015-12-06' AND '2016-12-19'
GROUP BY tr.seller_id ) weekly_totals
WHERE this_week_amount*2<=weekly_totals.following_week_amount

 

[nikhilboorla]

On 8/5/2019 at 11:50 AM, panipoori said:

Technical challenge ah in Interview? eh company?

 

On 8/5/2019 at 12:08 PM, panipoori said:

Tables clear gaane vndi kaani question is confusing kncham....

My take

With trans_dec_uk as

(

select s.seller_id,t.transaction_date, sum(t.Qty * Price) trans_amount

from transaction t join seller s

on t.seller_id =s.seller_id

where s.country ='UK'

and t.transaction_date between '12-05-2015' and '12-18-2015'

group by s.seller_id,t.transaction_date

)

This is the subest of the data.. I think you should use self join on this table to achieve the solution

 

 

I have tried below query but facing error when calculating sellers having twice the transaction amount from those sellers in following week.

With trans_dec_uk as

(

select s.seller_id,t.transaction_date, sum(t.Qty * Price) trans_amount

from transaction t join seller s

on t.seller_id =s.seller_id

where s.country ='UK'

and t.transaction_date between '12-05-2015' and '12-18-2015'

group by s.seller_id,t.transaction_date

)

select count(seller) from  trans_dec_uk

where trans_amount =  to_char(sysdate+7,'DD-MM')

[kingcasanova]

just asking

pakka single query lone motham lepaalaaa? paddathiga multiple queries tho one by one raasukokudada?

like

1.Get all seller IDs, 0 as week1_amt, 0 as week2_amt from that specific country

2.update week1_amt for those sellers

3.udpate week2_amt for them

4.get the goddamn comparision of the amounts

 

why complicate things??

[pachimirchi]

select distinct s.name from transactions t 

Inner join seller s on t.seller_id=s.id

where t.transaction_date between '2015-12-06'and ''2015-12-12 23:59:59' and s.county='UK'

and exists (select 1 from transactions t1 where t1.seller_id=t.seller_id and t1.transaction_date between dateadd ('week',1,'2015-12-06') and dateadd ('week',1,'2015-12-12 23:59:59') and (t1.qty*t1.price)>=(2*t.qty*t.price))

 

this should do it

[gunturfriends]

22 hours ago, gunturfriends said:

 

SELECT COUNT(1) FROM (
SELECT tr.seller_id
SUM(CASE WHEN transaction_date between '2015-12-06' AND '2015-12-10' THEN tr.Price*tr.Quantity ELSE 0 END) AS this_week_amount,
SUM(CASE WHEN transaction_date between '2015-12-13' AND '2015-12-19' THEN tr.Price*tr.Quantity ELSE 0 END) AS following_week_amount
FROM Transactions tr JOIN Seller s ON tr.seller_id=s.Seller_id WHERE s.country='UK' AND tr.transaction_date BETWEEN '2015-12-06' AND '2016-12-19'
GROUP BY tr.seller_id ) weekly_totals
WHERE this_week_amount*2<=weekly_totals.following_week_amount

 

idi work avvaleda?

[nikhilboorla]

16 hours ago, pachimirchi said:

select distinct s.name from transactions t 

Inner join seller s on t.seller_id=s.id

where t.transaction_date between '2015-12-06'and ''2015-12-12 23:59:59' and s.county='UK'

and exists (select 1 from transactions t1 where t1.seller_id=t.seller_id and t1.transaction_date between dateadd ('week',1,'2015-12-06') and dateadd ('week',1,'2015-12-12 23:59:59') and (t1.qty*t1.price)>=(2*t.qty*t.price))

 

this should do it

This works well..thanks