Interview Questions

Jessica C. - "select customer_id, order_date, orderid as earliestorder_id from ( select customer_id, order_date, order_id, rownumber() over (partition by customerid, orderdate order by orderdate) as orderrankper_customer from orders ) sub_table where orderrankper_customer=1 order by orderdate, customerid; Standard solution assumed that the orderid indicates which order comes in first. However this is not always the case, and sometime orderid can be random number withou"See full answer

Anonymous Tortoise - "SELECT i.item_category, o.order_date, SUM(o.orderquantity) AS totalunits_ordered FROM orders o JOIN items i ON o.itemid = i.itemid WHERE o.order_date >= DATE('now', '-6 days') GROUP BY i.item_category, o.order_date ORDER BY i.item_category ASC, o.order_date ASC;"See full answer

Salome L. - "SELECT items.item_category, SUM(orders.orderquantity) AS totalunitsorderedlast7days FROM orders JOIN items ON orders.itemid = items.itemid WHERE orders.order_date BETWEEN DATE('now', '-6 days') AND DATE('now') GROUP BY items.item_category `"See full answer

Rudra pratap S. - "select sum(orderquantity) as totalunitsorderedyesterday from orders as ord join items as it on ord.itemid=it.itemid where order_date="2023-10-14""See full answer

E L. - "WITH CTE AS ( SELECT *, ROWNUMBER()OVER(PARTITION BY utxoid ORDER BY transactionid) AS trxrk FROM transactions JOIN transaction_inputs USING (transaction_id) JOIN utxo USING (utxo_id) ) SELECT transaction_id AS InvalidTransactionId FROM CTE WHERE sender!=address OR trx_rk > 1 `"See full answer

Alvin P. - "WITH logins AS(SELECT user_id, timestamp, RANK() OVER(PARTITION BY userid ORDER BY timestamp ASC) AS loginorder FROM useractivitylog WHERE activity_type = 'LOGIN') SELECT l1.user_id, l1.timestamp AS current_login, l2.timestamp AS previous_login, (strftime('%s', l1.timestamp) - strftime('%s', l2.timestamp)) / 60 AS minutes_elapsed FROM logins AS l1 JOIN logins AS l2 ON l1.userid = l2.userid AND l1.loginorder - l2.loginorder = 1 GROUP BY l1.user_id,"See full answer

Evan R. - "The user table no longer exists as expected - I get an error that user does not contain user_id. Note that querying the table results in only user:swuoevkivrjfta select * FROM user `"See full answer

Anonymous Kingfisher - "The interviewer focused on the data models and steered the conversation there from the beginning. He had a specific design in mind and I just wasn't hitting it. He also wanted details on how I would implement the logic for splitting users into variants. I would recommend reading the LinkedIn blog on their AB Test system design."See full answer

Ajay P. - "Designing an evaluation framework for ads ranking is crucial for optimizing the effectiveness and relevance of ads displayed to users. Here's a comprehensive framework that you can use: Define Objectives and Key Performance Indicators (KPIs):** \\Click-Through Rate (CTR):\\ The ratio of clicks to impressions, indicating the effectiveness of an ad in attracting user attention. \\Conversion Rate:\\ The ratio of conversions (e.g., sign-ups, purchases) to clicks, measuring how well"See full answer

Kartikeya N. - "C : Okay. So I would want to start with knowing what is the product for which we have to build a recommendation system. I : This is a photo sharing product. C : Okay. So is this something on the lines of Instagram? I : Yes C : Okay. And are we a new product co or we have some current product built already? I : You can assume yourself. C : Okay. Is there any demography or country we are targeting? I : No, this is a global product C : Okay. So, the biggest goal of any product recommendation system"See full answer

Sai vuppalapati M. - "At a high level, the core challenge here revolves around building an effective recommendation algorithm for news. News is an inherently diverse category, spanning various topics and catering to a wide array of user types and personas, such as adults, business professionals, general readers, or specific cohorts with unique interests. Consequently, developing a single, one-size-fits-all recommendation algorithm is not feasible. To enhance the personalization of the news recommendation algorithm,"See full answer

Zain R. - "I've worked on projects not quite like this, but very similar, in the past - I'll borrow from that to answer this: The Broader Context this problem doesn't specify the type of data we're working with, or how it's being ingested to align with my personal background, I'll assume a picture that lends this problem well to being a computer vision (abbreviated "CV") related question: let's say we have a conveyor belt in a waste facility, which sequentially carries a stream of waste w"See full answer

Riku H. - "Great question - this is similiar to the "should uber go into rides for kids?" type of question so it's mostly strategy but also part design the way the question is phrased. Purely design would be " Tell me how you would build uber for kids" Assumption here is Uber in India functions the same as anywhere else in the world, is that correct? Yes, but in India they also take cash payments OK, what about safety regulations like car seats? (needs clarificaitons) What about driver licensing in Ind"See full answer

Salome L. - "SELECT MIN(id) AS id, TRIM(LOWER(email)) AS cleaned_email FROM users GROUP BY cleaned_email ORDER BY id `"See full answer

Akshay D. - "SELECT u.user_id, u.user_name, u.email, ROUND(AVG(CASE WHEN b.status = 'Unmatched' THEN 1.0 ELSE 0 END), 2) AS avgunmatchedbookings FROM users u LEFT JOIN bookings b ON u.userid = b.userid GROUP BY u.user_id, u.user_name, u.email; `"See full answer

Alexey T. - "-- The text of the task is a bit confusing. If the status is repeated several -- times, then in the end you should show as start_date the date of the first -- occurrence, and in end_date the date of the last occurrence of this status, -- and not the date of the beginning of the next status with t1 as (select order_id, status, orderdate as startdate, lead(orderdate) over (partition by orderid order by orderdate) as enddate, ifnull(lag(status) over (partition by order_id order by or"See full answer

Harshi B. - "SELECT DISTINCT title, ROUND(AVG(rating) over (partition by title),1) avg_rating, ROUND(AVG(rating) over (partition by genre),1) genre_rating FROM rating r JOIN movie m ON r.movieid=m.movieid ORDER by 1"See full answer

Lucas G. - "select sub.name subreddit_name, count(distinct us.userid) totalusers from user_subreddit as us left join subreddit as sub on us.subredditid = sub.subredditid group by us.subreddit_id having count(distinct us.user_id) > 3"See full answer