Interview Questions

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

Gowtami K. - "with cte as ( select user_id, timestamp as current_login, lag(timestamp,1) over(partition by userid order by timestamp) as previouslogin , round(abs(julianday(timestamp)-julianday(lag(timestamp,1) over(partition by userid order by timestamp)))2460)as minuteselapsed from useractivitylog where activity_type ='LOGIN' ) select userid, currentlogin, previouslogin, minuteselapsed from cte where currentlogin previouslogin `"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

Lin R. - "Why can not group by only on name? it gave me incorrect results when I try to do that."See full answer

Lucas G. - "SELECT COUNT(*) unique_conversations FROM messenger_sends WHERE senderid < receiverid"See full answer