GoComet Interview Experience for SDE-2 (1.5 Years Experienced)

Coding Round:
1. Write SQL Query to get several overlaps for each user. schema employee(user_id,start_date,end_date)
The output will be for each user_id to get a count of other users which have overlapping start_dates and end_dates 
2. Design a system for myntra where whenever there is a price drop( means seller reduces the price of product), the user will get an alert that this product price has been reduced if it is there in the user’s cart. Make a scalable system for it. 
3. https://www.geeksforgeeks.org/count-of-substrings-of-length-k-with-exactly-k-distinct-characters/
4. https://www.geeksforgeeks.org/write-a-c-program-to-print-all-permutations-of-a-given-string/

Round 1: We have an auction service that gives a source, destination, and shipping dates returns a list of vendors with various quotations which have cost prices for carrying goods. Each vendor can have multiple quotations with different prices for the same dates.  A quotation will have valid for and valid till dates. Any shipping between the range will cost the amount mentioned in the quotations

• Get_average(source, destination, current_date) – > return average rates for entire week
• Get-average_last_13Weeks(source destination, current_date) – > return average rates for last 12 weeks including current week

Flow:
1. User provides the source, destination, and current_date
2. Before passing to our business layer, we can convert this current_date to a range for the start and end of the week.
3. Now we have the parameters desired which can be passed to our auction system.
4. Our Response will be priced by each vendor which provides shipping service in that particular range.
5. In our business logic we can easily calculate the average price by the total sum of all rates with a total number of possible different prices.
6. {“source”: “xyz”, “destination”: “abc”, “date-range”: “”, “vendors”: [{“q1”: price1, “q2”: price2}, {“q1”: price1}, {“”}]}

Enquiry Table:
• Source [port id belonging to Port table]
• Destination  [port id belongs to Port table]
• Shipper_id [User id belongs to User table]
• Shipping Date 
• ID
Quote Table:
• Enquiry_id [FK]
• Vendor-Id [User ID belongs to User table]
• Cost [in USD]
• Valid from 
• Valid till
• Carrier Id [belongs to carrier table]
• ID

Given a source, destination, and date, how will you generate the response at pointer 6? Select source, destination, shipping_date, vendor.id, Quote.id, cost from enquiry join quote on enquiry.id=quote.id where source=source and destination=destination and date between valid_from and valid_till group by vendor_id;