Harsh Mehta

#collected Netflix Analytics Engineer Interview Experience: CTC - 35 LPA SQL Questions: 1️⃣ SQL Question 1: Identify VIP Users for Netflix Question: To better cater to its most dedicated users, Netflix would like to identify its “VIP users” - those who are most active in terms of the number of hours of content they watch. Write a SQL query that will retrieve the top 10 users with the most watched hours in the last month. Tables: • users table: user_id (integer), sign_up_date (date), subscription_type (text) • watching_activity table: activity_id (integer), user_id (integer), date_time (timestamp), show_id (integer), hours_watched (float) 2️⃣ SQL Question 2: Analyzing Ratings For Netflix Shows Question: Given a table of user ratings for Netflix shows, calculate the average rating for each show within a given month. Assume that there is a column for user_id, show_id, rating (out of 5 stars), and date of review. Order the results by month and then by average rating (descending order). Tables: • show_reviews table: review_id (integer), user_id (integer), review_date (timestamp), show_id (integer), stars (integer) 3️⃣ SQL Question 3: What does EXCEPT / MINUS SQL commands do? Question: Explain the purpose and usage of the EXCEPT (or MINUS in some SQL dialects) SQL commands. 4️⃣ SQL Question 4: Filter Netflix Users Based on Viewing History and Subscription Status Question: You are given a database of Netflix’s user viewing history and their current subscription status. Write a SQL query to find all active customers who watched more than 10 episodes of a show called “Stranger Things” in the last 30 days. Tables: • users table: user_id (integer), active (boolean) • viewing_history table: user_id (integer), show_id (integer), episode_id (integer), watch_date (date) • shows table: show_id (integer), show_name (text) 5️⃣ SQL Question 5: What does it mean to denormalize a database? Question: Explain the concept and implications of denormalizing a database. 6️⃣ SQL Question 6: Filter and Match Customer’s Viewing Records Question: As a data analyst at Netflix, you are asked to analyze the customer’s viewing records. You confirmed that Netflix is especially interested in customers who have been continuously watching a particular genre - ‘Documentary’ over the last month. The task is to find the name and email of those customers who have viewed more than five ‘Documentary’ movies within the last month. ‘Documentary’ could be a part of a broader genre category in the genre field (for example, ‘Documentary, History’). Therefore, the matching pattern could occur anywhere within the string. Tables: • movies table: movie_id (integer), title (text), genre (text), release_year (integer) • customer table: user_id (integer), name (text), email (text), last_movie_watched (integer), date_watched (date)

8

2 Comments

Here is a very interesting question asked in Probo for a senior data analyst position. We have a table which has 5 columns. a. User_id, poll_id, poll_option_id, amount, created_dt b. Users invest money on these different poll options - these entries are there in the table c. One of the options is the outcome of the event. Example : Event: How many matches will the Indian cricket team win in 2022? A) Less than 50 B) 50-60 C) 61-65 D) Greater than 65 India wins 63 matches in the end. Option C is the winner. Money invested in option A, B and D should be proportionately distributed amongst users who invested money in option C. For example - If the total money invested in option A, B and D is Rs. 1500 and there are 3 users who invested 500 in option C {250, 200 and 50} These users would receive (750, 600 and 150 respectively) - sum is 1500. Write down a query for the above settlement process. Check out my detailed explanation in the comments section. #sql #analytics

161

25 Comments

𝐇𝐨𝐰 𝐭𝐨 𝐛𝐞 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭 𝐢𝐧 90 𝐝𝐚𝐲𝐬? Only 91 days left to complete 2024 and you are thinking that you couldn't use this year properly to complete your Data Analytics track. Here is your 90 days plan to complete the Data Analytics track before 2024 ends! 𝐄𝐱𝐜𝐞𝐥 (𝐃𝐚𝐲𝐬 1-15): SUM, AVERAGE, COUNT, COUNTA, MAX, MIN IF, AND, OR, IFERROR VLOOKUP, HLOOKUP, XLOOKUP CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM Data Validation Conditional Formatting Sorting and Filtering Pivot Tables and Pivot Charts Waterfall, Histogram Data Cleaning: Remove Duplicates, Text to Columns Excel Dashboards 𝑬𝒙𝒄𝒆𝒍 𝑷𝒓𝒐𝒋𝒆𝒄𝒕: 𝑪𝒓𝒆𝒂𝒕𝒆 𝒂𝒏 𝒊𝒏𝒕𝒆𝒓𝒂𝒄𝒕𝒊𝒗𝒆 𝒅𝒂𝒔𝒉𝒃𝒐𝒂𝒓𝒅 𝒖𝒔𝒊𝒏𝒈 𝑷𝒊𝒗𝒐𝒕 𝑻𝒂𝒃𝒍𝒆𝒔, 𝑪𝒉𝒂𝒓𝒕𝒔, 𝒂𝒏𝒅 𝑪𝒐𝒏𝒅𝒊𝒕𝒊𝒐𝒏𝒂𝒍 𝑭𝒐𝒓𝒎𝒂𝒕𝒕𝒊𝒏𝒈. 𝐒𝐐𝐋 (𝐃𝐚𝐲𝐬 16-40): Basic Database, DDL,DML,DCL, CREATE,INSERT,UPDATE,DELETE SELECT, WHERE, ORDER BY GROUP BY, HAVING DISTINCT, COUNT, SUM, AVG, MIN, MAX SQL Functions: COALESCE, NULLIF, CASE Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF JOIN Subqueries UNION, INTERSECT, EXCEPT Window Functions: ROW_NUMBER, RANK, DENSE RANK, PARTITION, LED,LAG Window Functions using Aggregations - SUM,COUNT, Stored Procedures 𝑷𝒓𝒐𝒋𝒆𝒄𝒕 - 𝑷𝒓𝒂𝒄𝒕𝒊𝒄𝒆 𝒔𝒄𝒆𝒏𝒂𝒓𝒊𝒐 𝒃𝒂𝒔𝒆 𝒒𝒖𝒆𝒔𝒕𝒊𝒐𝒏𝒔 𝒇𝒓𝒐𝒎 𝒅𝒊𝒇𝒇𝒆𝒓𝒆𝒏𝒕 𝒔𝒊𝒕𝒆. 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 (𝐃𝐚𝐲𝐬 41-60): Introduction to Power BI Interface Importing Data from Different Sources Data Transformation with Power Query Creating Relationships between Tables Creating Basic Visuals: Bar, Line, Pie Charts Filters and Slicers Creating Calculated Columns and Measures DAX Basics: SUM, COUNT, AVERAGE Time Intelligence in DAX Power BI Dashboards and Reports Sharing and Publishing Reports to Power BI Service 𝑷𝒐𝒘𝒆𝒓 𝑩𝑰 𝑷𝒓𝒐𝒋𝒆𝒄𝒕: 𝑩𝒖𝒊𝒍𝒅 𝒂𝒏 𝒊𝒏𝒕𝒆𝒓𝒂𝒄𝒕𝒊𝒗𝒆 𝒔𝒂𝒍𝒆𝒔 𝒅𝒂𝒔𝒉𝒃𝒐𝒂𝒓𝒅. 𝑪𝒐𝒏𝒏𝒆𝒄𝒕 𝒚𝒐𝒖𝒓 𝑫𝑩(𝒐𝒏 𝑺𝑸𝑳 𝒔𝒆𝒄𝒕𝒊𝒐𝒏𝒔 𝒚𝒐𝒖 𝒉𝒂𝒗𝒆 𝒖𝒔𝒆𝒅 𝒊𝒕) 𝒕𝒐 𝑷𝒐𝒘𝒆𝒓 𝑩𝑰 𝒂𝒏𝒅 𝒄𝒓𝒆𝒂𝒕𝒆 𝒂 𝒅𝒂𝒔𝒉𝒃𝒐𝒂𝒓𝒅 𝒐𝒖𝒕 𝒐𝒇 𝒊𝒕. 𝐏𝐲𝐭𝐡𝐨𝐧 (𝐃𝐚𝐲𝐬 61-85): Introduction to Python: Variables, Data Types Conditional Statements and Loops Functions and Lambda Functions Lists, Tuples, Dictionaries Working with Files: Reading/Writing Files NumPy: Arrays and Operations Pandas: DataFrames, Series, Basic Operations Data Visualization with Matplotlib and Seaborn 𝑷𝒚𝒕𝒉𝒐𝒏 𝑷𝒓𝒐𝒋𝒆𝒄𝒕: 𝑨𝒏𝒂𝒍𝒚𝒛𝒆 𝒂𝒏𝒅 𝒗𝒊𝒔𝒖𝒂𝒍𝒊𝒛𝒆 𝒔𝒂𝒍𝒆𝒔 𝒅𝒂𝒕𝒂 𝒖𝒔𝒊𝒏𝒈 𝑷𝒂𝒏𝒅𝒂𝒔, 𝑴𝒂𝒕𝒑𝒍𝒐𝒕𝒍𝒊𝒃, 𝒂𝒏𝒅 𝑺𝒆𝒂𝒃𝒐𝒓𝒏. 𝐅𝐢𝐧𝐚𝐥 𝐏𝐫𝐨𝐣𝐞𝐜𝐭(86-90): Guided Project - Pick any of the guided project from internet consists of SQL, Python and Power BI. 2. Unguided Project - Pick a dataset , load it into a DB, write SQL queries to get the data then use Power BI/Python for the final outcome. Or, you can use both. Tips - Don't forget to revise in every Weekends. Data source- https://lnkd.in/gt7SDwZw Happy Learning Shubhrajit Basu

82

17 Comments

Tiger Analytics Data Analyst Interview Experience (1-3 Years Experience) 👇 1. Tell me about your background and current role. 2. Rate your SQL proficiency from 1 to 10 and justify your rating. 3. Write an SQL query to find the highest-paid employee in each department who joined in the last 3 years. 4. Explain window functions in SQL and provide an example of ranking products by quarterly revenue. 5. How would you merge two DataFrames in Python and calculate the average order value for each customer? 6. Write a Python code snippet to calculate cumulative revenue using Pandas. 7. Describe a project where you used Power BI for data visualization. 8. What challenges have you faced while working with large datasets, and how did you solve them? 9. How would you resample a time series dataset to get monthly totals in Python? 10. Discuss a time when you faced difficulties during a project and how you overcame them. 11. How do you handle missing data in a dataset, and what methods do you use for imputation? 12. Explain how you would optimize a slow SQL query. 13. Describe your experience with data cleaning and the steps you follow. 14. How would you validate the accuracy of a data model or report? 15. What key metrics would you track when analyzing the performance of a business using data? Now, if you’re looking for a platform to help you with your end-to-end interview preparation, you can check out the programs by Bosscoder Academy. Check the complete program here: ✨Here’s the exciting part get 30% off with a festive discount on all of their programs ✨ They can help you with → https://lnkd.in/ghchysUz ✅ Structured curriculum to learn Data tools, Machine Learning, Deep Learning and more. ✅ Personal mentorship from experienced Data professionals. ✅ Live classes, project-based learning, and access to a talented community of people with similar goals of upskilling. Also, get placement support post-completion of the program #DataAnalytics #InterviewQuestions #collab

90

2 Comments

🚀 Referral Alert @ Nielsen! 🚀 Hello friends , Nielsen is hiring for below positions 1. Statisticians 2. Machine learning Engineer 3. Machine Learning Researcher 4. tech Data Analysts if you are interested for above Roles . Please send your CV in following format NAME_year_roleName.pdf (eg : AtanuChowdhury_3year_MachineLearningEngineer.pdf) to [email protected] for referral or you can send to my inbox. freshers can also apply. #DataScience #MachineLearning #Statistics #NLP #DeepLearning

29

6 Comments

What are the 5 career path in Data Analytics that will make good money: 1. Business Analyst (up to 108k) 2. Data Analyst (up to 167k) 3. DB Administrator (up to 117k) 4. Data Research (up to 128k) 5. Data Scientist, Analytics or Product Analyst (up to 175k) My personal favorite is the number of 5, BECAUSE: You can make up to 300-400k in FAANG and even more. And this role oversees the entire process and life cycle of a product, from sizing the opportunity to getting the product on the shelves! SO you make a LOT of impact and get TONS of visibility! What's your favorite (even if it's not on the list) or the one you're aiming for?

44

3 Comments

Myntra Data Analyst Interview Experience (1-3 years): Round 1 Elimination Resume Grilling + SQL: 1️⃣ 2-3 SQL Question, around self join , usage of Ranking functions and one case specific question around Lead/lag window function 2️⃣ Myntra wants to improve its data quality by identifying duplicate or similar product listings in their inventory. Due to slight variations in product titles (such as spelling differences, abbreviations, or extra spaces), some products might appear more than once in the inventory. You are tasked with identifying pairs of products that are likely duplicates based on their titles, using fuzzy matching. Round 2 Case Study 1️⃣ A basic guess estimate question, Estimate the number of Uber Cars are there in Bengaluru? 2️⃣ Myntra, wants to solve the issue pertaining to fraudulent customer behavior, list down all the metrics and provide some actionable on solving this issue ⏩This round also had resume grilling Round 3 (Hiring Manager): 1️⃣Resume Grilling around the projects being mentioned on resume. 2️⃣Behavioral questions and whether you would be the right fit at Myntra Round 4 HR Discussion 1️⃣ Discussion around salary, joining date and what culture to expect at Myntra. Follow Digvijay Rajput For More.

216

23 Comments

Top Data Analyst Interview Question. 1. Find Users with Max Revenue per category? Table Name : Sales (user_id, category, revenue) --> SELECT    user_id,   category,   revenue,   MAX(revenue) OVER (PARTITION BY category) AS max_revenue_per_category FROM    Sales WHERE   revenue = MAX(revenue) OVER (PARTITION BY category) ORDER BY    category, revenue DESC; Please feel free to share any alternative approaches or suggestions in the comments below. #sql #database 😊 Follow Tejaswini Yeola For more.

18

2 Comments

There have been crunches in the Job market. As experienced data professionals, we used to get multiple calls in a day even without taking any steps. Now things are different. If you are someone who has been looking for a job but not getting it; keep the below points in check: ✅ Resume is up-to-date with a good ATS score. Stress more on the right numbers and projects. ✅ Naukri's profile is 100% with the right keywords. ✅ LinkedIn profile is active with resume and contact details. ✅ Skills set at track. You can crack basic to intermediate technical questions. ✅ Proactively reaching out to people and recruiters every day. ✅ Added yourself to different job platforms; like - tophire, bigshyft, etc. ✅ Communication and soft skills. ✅ Right mental attitude. The rest are not in your hands. Don't blame yourself for the bad market conditions. Keep things in check, and stay positive.

52

5 Comments

Tiger Analytics Data Analyst Interview Experience (0-3 years) SQL Questions: 1. Describe a scenario where you used SQL to analyze customer data. What insights did you uncover? 2. Rate your SQL skills on a scale of 1-10 and provide examples of advanced queries you’ve written. 3. Write a query to identify the second-highest salary in each department. 4. Explain the concept of JOINs and provide a query that joins three tables (Orders, Customers, Products) to find the top 5 customers by revenue. 5. What is a Common Table Expression (CTE) in SQL, and when would you use it? Write a CTE query to calculate cumulative monthly sales. 6. Write an SQL query to find all employees whose salaries are above the department average. 7. Describe your approach to optimizing SQL queries. Can you share an example where optimization made a noticeable difference? Python Questions: 1. What Python libraries do you frequently use for data manipulation, and why? 2. How would you write a Python function to calculate moving averages for sales data? 3. Write a Pandas code snippet to remove outliers from a dataset based on the IQR method. 4. Describe a project where you used Matplotlib or Seaborn for data visualization. What insights did your visualizations reveal? 5. How would you merge three DataFrames (Sales, Customers, Regions) and compute the average sales per region? 6. Write a Python code snippet to group data by product category and calculate total revenue for each category. 7. How do you handle missing data in Python? Share a few imputation techniques you use. 8. Explain how you would use time series analysis in Python for forecasting monthly sales. Follow Digvijay Rajput For More.

328

17 Comments

My first Analytics job came after: - 100+ Rejections on my applications - ~10 initial HR Discussion - 5 First round technical rejection - 2 Rejections in final round - 1 offer There are 4 key lessons to take away from this: 1) You need only one offer to get started in your career, so don't worry about the countless rejections you'll face in the initial journey! 2) Improvise from your mistakes, if the #resume is not getting shortlisted take few steps back and rework ! If you are not able to explain the projects clearly, work on it! [ This is an iterative process) 3) Rejections and setbacks are often blessings in disguise, leaving room for improvement 4) Trust the process, you'll eventually find a way! you can follow Gaurav Agrawal for more ! #jobsearch #analytics #datafam

468

24 Comments

Sanjana Antalmarad from Zepto is hiring for Senior Product Manager! There is simply too much friction reading a resume right now, so the best way for you to land interviews is by direct message on LinkedIn BUT LET ME BE CRYSTAL CLEAR. You still have to mass apply, because when companies ask you if you have applied yet in the portal and you say no then you have to start from scratch. It’s a lot of work I know and it sucks, but you got to do it. In 2025 mark my words at the rate AI is moving you will all by interviewing with holograms. I am not joking at all. There will be AI recruiting software that will sort, sift, screen (oh yes interview you), and schedule the second round interview. This is why I am telling everybody to land this job as soon as possible and to start a brand (more on that later), but for now you need to deliver a more impactful message to pitch your resume. Well… Rich what the heck am I supposed to say? That’s a fair question and I got you covered right here. I have a list of 33 openers you can use in Skool. See you in Skool! https://lnkd.in/gtQjCfUH Don’t be late to Skool! https://lnkd.in/gtQjCfUH PS Please RESHARE this post for all your colleagues still looking for a job. #productmanagement #productmanagementcareers #productmanagementjobs #speedtolead

2

FP&A Insights: Entering/Switching to FP&A Applicable for 0-3 yrs work ex professionals I receive a lot of DMs from early career professionals around how to enter/switch to FP&A domain from their current role Here are the key insights that my stay in this domain has taught me and what a hiring manager looks forward to: 1) Plain, crisp and clear CV; because that’s the inherent characteristic of a FP&A candidate. Personal but strong recommendation: keep it to one page PDF 2) Demonstate in CV how have you resolved a problem and helped management land at a quality decision. Example: Designed a dashboard for real time tracking of xyz resulting in saving of xx$ 3) Display that you have a strong financial analysis background. Be it a case study that you solved in your college or you scored good academically in financial mgt or you did an analysis in your current company and improved productivity/profitability 4) Exhibit business understanding. Example: how your skillset can marry the target role and company. Example: Someone from Contracting background can display expertise of mitigating risk of revenue loss and also accurate revenue forecast. 5) Show how comfortable and equipped are you with excel, ppt, Power BI and related visualization tools. Hope above points help land your FP&A adventure soon!! #fpa #jobinsights

136

2 Comments

As a data analyst , have you ever performed AB testing in operations? While working in the startup , I have worked on the Delivery Fee Analysis project using AB Testing. Here the crux is you have to run experiment on multiple phases -- > Because delivery fee has a great impact on customer churn. So you cannot roll out using 50% We run it in 3 Phases Phase 1: 10% experiment and 90% control for 4 weeks Phase 2: With some improvement and learnings-- 30% experimental group and 70 % control group Phase 3: 50%, 50% equally distributed Then Roll out the model AB testing in supply chain operations, mostly used for identifying delivery fees, rate cards. But before running an experiment , you need to do a strong analysis and then on the basis of number , you take the decision because 10% is also a great number

49

8 Comments