Often queries in SQL are asked to fetch data in certain conditions. The intention of the interviewer is to identify if the candidate is able to come up with the right set of queries, using the right joins or sub-queries. It’s relatively easy to answer what is a self join than to figure out wherever you have to use one.
Case study: E-commerce data, in a table “sales” with 5 columns: cust_id, order_id, order_date, order_amount, curr_loc_city
- Find out top 5 customers of each city, by the total sales.
- Find out top 5 customers of each city who order the most.
- Generate a table, that has cust_id (all distinct customers) and latest_city (city from which the latest order was made)