Tripti Jain

Microsoft Data Analyst Interview Experience(1-5 years) : CTC - 35 LPA SQL Questions: 1️⃣ Most Popular Client ID: Select the most popular client_id based on a count of the number of users who have at least 50% of their events from the following list: ‘video call received’, ‘video call sent’, ‘voice call received’, ‘voice call sent’. 2️⃣ Desktop-Only Users: Write a query that returns the company (customer_id column) with the highest number of users that use desktop only. 3️⃣ Bottom Companies by Mobile Usage: Write a query that returns a list of the bottom 2 companies by mobile usage. Company is defined in the customer_id column. Mobile usage is defined as the number of events registered on a client_id == 'mobile'. Order the result by the number of events ascending. In the case where there are multiple companies tied for the bottom ranks (rank 1 or 2), return all the companies. Output the customer_id and number of events. 4️⃣ Exclusive Users per Client: Write a query that returns a number of users who are exclusive to only one client. Output the client_id and number of exclusive users. 5️⃣ Unique Users per Client per Month: Write a query that returns the number of unique users per client per month. 6️⃣ Monthly User Share (New vs. Existing): Calculate the share of new and existing users for each month in the table. Output the month, share of new users, and share of existing users as a ratio. New users are defined as users who started using services in the current month (there is no usage history in previous months). Existing users are users who used services in the current month but also used services in any previous month. Assume that the dates are all from the year 2020. Table Structure • id: int64 • time_id: datetime64[ns] • user_id: object • customer_id: object • client_id: object • event_type: object • event_id: int64 Feel free to share your answers or thoughts in the comments. Follow Digvijay Rajput For More.

550

38 Comments

Microsoft Data Analyst Interview Experience(1-5 years) : CTC - 35 LPA SQL Questions: 1️⃣ Most Popular Client ID: Select the most popular client_id based on a count of the number of users who have at least 50% of their events from the following list: ‘video call received’, ‘video call sent’, ‘voice call received’, ‘voice call sent’. 2️⃣ Desktop-Only Users: Write a query that returns the company (customer_id column) with the highest number of users that use desktop only. 3️⃣ Bottom Companies by Mobile Usage: Write a query that returns a list of the bottom 2 companies by mobile usage. Company is defined in the customer_id column. Mobile usage is defined as the number of events registered on a client_id == 'mobile'. Order the result by the number of events ascending. In the case where there are multiple companies tied for the bottom ranks (rank 1 or 2), return all the companies. Output the customer_id and number of events. 4️⃣ Exclusive Users per Client: Write a query that returns a number of users who are exclusive to only one client. Output the client_id and number of exclusive users. 5️⃣ Unique Users per Client per Month: Write a query that returns the number of unique users per client per month. 6️⃣ Monthly User Share (New vs. Existing): Calculate the share of new and existing users for each month in the table. Output the month, share of new users, and share of existing users as a ratio. New users are defined as users who started using services in the current month (there is no usage history in previous months). Existing users are users who used services in the current month but also used services in any previous month. Assume that the dates are all from the year 2020. Table Structure • id: int64 • time_id: datetime64[ns] • user_id: object • customer_id: object • client_id: object • event_type: object • event_id: int64 Feel free to share your answers or thoughts in the comments. Follow Digvijay Rajput For More.

401

15 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

Job switch tricks for target 2025 🏙️ ⛷️ Update your job profile every day for better visibility. ⛷️ Apply for the maximum relevant job openings every day. ⛷️ Add specific keywords related to the job (e.g., PySpark, ADF, Databricks, Snowflake etc.) to your resume. ⛷️ Check job descriptions to find the responsibilities and requirements. ⛷️ Reach out to HR directly via DMs or emails. ⛷️ Use multiple job portals and platforms like Naukri, Indeed, Monster, LinkedIn and referrals or friends networks. ⛷️ Create multiple job profiles based on your location preference. ⛷️ Replace career gaps with freelance work on your resume. The easiest way to increase your chances is to try more! follow Asheesh .. for more updates ✨ #jobs #career

65

7 Comments

Hi I'm sharing my Maersk #SeniorDataAnalyst Tech Interview Questions: SQL Question :📒 Q1: Write a query to assign a percentile rank to each employee based on their salary in descending order within each department : SELECT  DISTINCT employee_id, department_id, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS percentile_rank FROM employees Q2: Find Nth Highest Salary without Window Function : SELECT DISTINCT salary FROM employees ORDERBY salary DESC LIMIT 1 OFFSET 2 Q3:Write query to get Year-on-Year growth for each product: SELECT product_id, year, sales_amount, LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY year) AS previous_year_sales, CASE WHEN LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY year) IS NOT NULL  THEN ((sales_amount - LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY year)) / LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY year)) * 100  ELSE NULL  END AS yoy_growth_percentage FROM  sales_data ORDER BY  product_id, year Q4:Write a query to calculate the 3-month moving average of sales for each product: SELECT product_id, month, sales, AVG(sales) OVER (PARTITIONBY product_id ORDERBY month ROWS BETWEEN 2 PRECEDING AND CURRENTROW ) AS moving_avg_sales FROM product_sales; Q5:Write a query to list each employee and their manager using a self-join: SELECT  e1.employee_id AS employee, e1.name AS employee_name, e2.employee_id AS manager_id, e2.name AS manager_name FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id; Q6:Normal Joins Questions & What is the count of rows for inner join, left join , right join and full join: PYTHON Question:🖥️ Q1: Which modules are commonly used for webscraping a webpage. Q2: What is the encapsulation and Abstraction in Python OOPS. Q3:Explain how you would handle missing data in a Pandas DataFrame. Q4:How do you Join & Union two DataFrames in Pandas. Q5:How do you use lead and lag from SQL concepts in Python for time-series data analysis.(You can use the shift() function in Pandas to implement lead and lag). Q6:Diff b/w List , Tuple , Set & Dictionary. Q7:Write a script to get the Current datetime & Current day name. Q8:Write a Lambda function to get square root of a number. #sql #python #dataanalyst #amazon #interviewtips #analytics #advancesql #connection #interview Follow Hareesh Kumar for more insightful posts 🙂

24

Tech Nest Academy We are starting a new SQL weekend batch from 28th Sept, 2024. Course Details: Days: Sat & Sun Time: 9 to 10:30 pm IST Duration: 2 Months Paid Course: Yes Salient Features: 1. Lifetime recording access 2. Live interactive session. 3. Job and placement support. 4. No prerequisite is needed to join this course. 5. Course will start from scratch. 6. Well suited for non technical folks as well. 7. Assignments based on real time industry projects. Interested folks are requested to join the group mentioned in the comment. Flat 60% discount in course fees for college students with valid college id card. #sql #sqltraining #dataanalyst #corporatetraining

10

2 Comments

SQL Challenge-Day 12🚀 Hello everyone! *CREATE TABLE Products(   product_id INT PRIMARY KEY,   product_name VARCHAR(100),   category VARCHAR(100),   price DECIMAL(10, 2) ); *INSERT INTO Products(product_id, product_name, category, price) VALUES (1, 'Laptop', 'Electronics', 999.99), (2, 'Smartphone', 'Electronics', 799.99), (3, 'Headphones', 'Electronics', 199.99), (4, 'Refrigerator', 'Appliances', 1499.99), (5, 'Microwave', 'Appliances', 299.99), (6, 'T-shirt', 'Clothing', 19.99), (7, 'Jeans', 'Clothing', 49.99), (8, 'Blender', 'Appliances', 99.99), (9, 'Coffee Maker', 'Appliances', 79.99), (10, 'Shoes', 'Clothing', 89.99); *CREATE TABLE product_sales(   sale_id INT PRIMARY KEY,   product_id INT,   customer_id INT,   sale_date DATE,   quantity INT,   amount DECIMAL(10, 2),   FOREIGN KEY (product_id) REFERENCES Products(product_id) ); *INSERT INTO product_sales (sale_id, product_id, customer_id, sale_date, quantity, amount) VALUES (1, 1, 101, '2023-01-15', 1, 999.99), (2, 2, 102, '2023-02-16', 2, 1599.98), (3, 3, 103, '2023-03-17', 3, 599.97), (4, 4, 104, '2023-04-18', 1, 1499.99), (5, 5, 105, '2023-05-19', 2, 599.98), (6, 6, 101, '2023-06-20', 5, 99.95), (7, 7, 102, '2023-07-21', 3, 149.97), (8, 8, 103, '2023-08-22', 1, 99.99), (9, 9, 104, '2023-09-23', 2, 159.98), (10, 10, 105, '2023-10-24', 1, 89.99), (11, 1, 101, '2023-11-24', 1, 999.99), (12, 2, 102, '2023-11-25', 2, 1599.98), (13, 3, 103, '2023-11-26', 3, 599.97), (14, 4, 104, '2023-11-27', 1, 1499.99), (15, 5, 105, '2023-11-28', 2, 599.98); *CREATE TABLE Customer (   customer_id INT PRIMARY KEY,   customer_name VARCHAR(100),   customer_city VARCHAR(100),   customer_state VARCHAR(100) ); *INSERT INTO Customer(customer_id, customer_name, customer_city, customer_state) VALUES (101, 'Alice', 'New York', 'NY'), (102, 'Bob', 'Los Angeles', 'CA'), (103, 'Charlie', 'Chicago', 'IL'), (104, 'David', 'Houston', 'TX'), (105, 'Eve', 'Phoenix', 'AZ'); *Challenge: Write a query to calculate the average monthly sales for each category.return category that has highest average sale in each month with rank_sales as ( select extract(month from s.sale_date) as months,p.category, avg(s.amount) as avg_sales, rank() over (partition by extract(month from s.sale_date) order by avg(s.amount)) as rn from products p join product_sales s on p.product_id=s.product_id group by extract(month from s.sale_date),p.category order by extract(month from s.sale_date),p.category ) select * from rank_sales where rn=1; Share your solutions in the comments below! Let's learn and grow together! Thank you for the guidance N H Follow for more such contents:https://lnkd.in/dpdvBV5F #SQLChallenge #DataAnalyst #DataAnalysis

6

#24/100 SQL PROBLEM STATEMENT: Write the query to find employees whose salary is greater than the average salary of employees in their respective location. DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (   EmpID INT PRIMARY KEY,   Emp_name VARCHAR(50),   Manager_id INT,   Salary DECIMAL(10, 2),   Location VARCHAR(50) ); INSERT INTO Employee (EmpID, Emp_name, Manager_id, Salary, Location) VALUES  (1, 'Alice', 3, 5000.00, 'New York'), (2, 'Bob', 3, 6000.00, 'New York'), (3, 'Charlie', NULL, 7000.00, 'New York'), (4, 'David', 5, 4000.00, 'Chicago'), (5, 'Eva', NULL, 6500.00, 'Chicago'), (6, 'Frank', 5, 4500.00, 'Chicago'), (7, 'Grace', 9, 5500.00, 'Los Angeles'), (8, 'Henry', 9, 6000.00, 'Los Angeles'), (9, 'Isaac', NULL, 7000.00, 'Los Angeles'); SELECT EmpID, Emp_name, Salary, Location FROM Employee AS e WHERE Salary > (   SELECT AVG(Salary)   FROM Employee   WHERE Location = e.Location ); #SQL #SQLCHALLENGE #SQLDAILY #ANALYTICS #BETTEREVERYDAY #DataAnalysis #SQLQuery #Database #DataAnalyst 

9

1 Comment

Meesho Data Analyst interview experience (0-3) - Power BI Questions: 1. Explain the concept of context transition in DAX and provide an example. 2. How would you optimize a complex Power BI report for faster performance? 3. Describe the process of creating and using calculation groups in Power BI. 4. Explain how you would handle large datasets in Power BI without compromising performance. 5. What is a composite model in Power BI, and how can it be used effectively? 6. How does the USERELATIONSHIP function work, and when would you use it? 7. Describe how to use Power Query M language for advanced data transformations. 8. Explain the difference between CROSSFILTER and TREATAS in DAX. SQL Questions: 1. How would you optimize a slow-running query with multiple joins? 2. What is a recursive CTE, and can you provide an example of when to use it? 3. Explain the difference between clustered and non-clustered indexes and when to use each. 4. Write a query to find the second highest salary in each department. 5. How would you detect and resolve deadlocks in SQL? 6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK. 7. Describe the ACID properties in database transactions and their significance. 8. Write a query to calculate a running total with partitions based on specific conditions. Follow Rituraj Singh Gour for more.

125

𝗔𝗛𝗧 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 𝗗𝗮𝘀𝗵𝗯𝗼𝗮𝗿𝗱 𝗳𝗼𝗿 𝗕𝗣𝗢 Overview: This dashboard presents a analysis of call handling metrics from a dataset of 5,000 calls in a BPO environment. It focuses on key performance indicators (KPIs) that provide insights into the efficiency and effectiveness of call handling across various departments and agents. 𝗞𝗲𝘆 𝗠𝗲𝘁𝗿𝗶𝗰𝘀 𝟭. 𝗔𝗻𝘀𝘄𝗲𝗿𝗲𝗱 %: • Displays the percentage of calls that were successfully answered by agents in each department. 𝟮. 𝗔𝗯𝗮𝗻𝗱𝗼𝗻𝗲𝗱 %: • Represents the percentage of calls that were abandoned before being answered. 𝟯. 𝗔𝘃𝗲𝗿𝗮𝗴𝗲 𝗛𝗮𝗻𝗱𝗹𝗶𝗻𝗴 𝗧𝗶𝗺𝗲 (𝗔𝗛𝗧): • Calculates the average time taken to handle calls 𝗗𝗲𝗽𝗮𝗿𝘁𝗺𝗲𝗻𝘁-𝗟𝗲𝘃𝗲𝗹 𝗜𝗻𝘀𝗶𝗴𝗵𝘁𝘀: The dashboard enables users to view metrics at the department level, facilitating comparisons between different teams within the organization. 𝗔𝗴𝗲𝗻𝘁-𝗟𝗲𝘃𝗲𝗹 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 The dashboard presents call handling metrics at the agent level: • Individual agent performance assessment, identifying high performers and those who may need additional training or support. 𝗖𝗼𝗻𝗰𝗹𝘂𝘀𝗶𝗼𝗻: AHT analysis dashboard serves as a powerful tool for BPO management, providing actionable insights into call handling performance. By leveraging key metrics such as Answered %, Abandoned %, and AHT, stakeholders can make informed decisions to enhance operational efficiency, improve customer service, and ultimately drive business success. #PowerBI, #BusinessIntelligence, #DataVisualization, #DataAnalytics, #BI, #DataDriven, #DataScience, #Analytics, #DataStorytelling, #PowerBIDashboard, #DataInsights, #Reporting, #DataAnalysis, #Visualization, #TechForGood, #Automation, #AHTAnalysis, #BPOAnalytics, #DigitalTransformation, #DataEngineering #KPI #AHT #AverageHandleTime #Dashboard #WFM #WFMMetrics #DataAnalysis #Reporting #DataVisualization

15

🔍 Harnessing ERD in SQL: A Quick Insight 📊 Entity-Relationship Diagrams (ERDs) visually represent the relationships between entities in a database system, pivotal for SQL database design. 🎯 Purpose: 1. **Entity Clarity**: ERDs define entities like customers, products, guiding data storage. 2. **Relationship Visualization**: They illustrate relationships (one-to-one, one-to-many), aiding foreign key establishment. 3. **Attribute Definition**: ERDs outline entity attributes, specifying stored data. 4. **Normalization Aid**: ERDs assist in normalizing databases, minimizing redundancy. 5. **Schema Representation**: ERDs depict database structure, facilitating collaboration and communication. Mastering ERDs in SQL empowers database professionals for scalable, efficient systems. 💡💻 #SQL #DatabaseDesign #ERD #DataModeling #TechInnovation #SQL #DatabaseDesign #ERD #DataModeling #TechInnovation #DatabaseManagement #DataEngineering #DataVisualization #DatabaseDevelopment #DataArchitecture #TechTrends #DataAnalytics #DataScience #DataManagement #SoftwareEngineering #TechCommunity #TechSkills #ProfessionalDevelopment #CareerGrowth #LinkedInNetworking

4

Meesho Data Analyst interview experience (0-3) - Power BI Questions: 1. Explain the concept of context transition in DAX and provide an example. 2. How would you optimize a complex Power BI report for faster performance? 3. Describe the process of creating and using calculation groups in Power BI. 4. Explain how you would handle large datasets in Power BI without compromising performance. 5. What is a composite model in Power BI, and how can it be used effectively? 6. How does the USERELATIONSHIP function work, and when would you use it? 7. Describe how to use Power Query M language for advanced data transformations. 8. Explain the difference between CROSSFILTER and TREATAS in DAX. SQL Questions: 1. How would you optimize a slow-running query with multiple joins? 2. What is a recursive CTE, and can you provide an example of when to use it? 3. Explain the difference between clustered and non-clustered indexes and when to use each. 4. Write a query to find the second highest salary in each department. 5. How would you detect and resolve deadlocks in SQL? 6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK. 7. Describe the ACID properties in database transactions and their significance. 8. Write a query to calculate a running total with partitions based on specific conditions. Follow Digvijay Rajput for more.

628

48 Comments

Meesho Data Analyst interview experience (0-3) - Power BI Questions: 1. Explain the concept of context transition in DAX and provide an example. 2. How would you optimize a complex Power BI report for faster performance? 3. Describe the process of creating and using calculation groups in Power BI. 4. Explain how you would handle large datasets in Power BI without compromising performance. 5. What is a composite model in Power BI, and how can it be used effectively? 6. How does the USERELATIONSHIP function work, and when would you use it? 7. Describe how to use Power Query M language for advanced data transformations. 8. Explain the difference between CROSSFILTER and TREATAS in DAX. SQL Questions: 1. How would you optimize a slow-running query with multiple joins? 2. What is a recursive CTE, and can you provide an example of when to use it? 3. Explain the difference between clustered and non-clustered indexes and when to use each. 4. Write a query to find the second highest salary in each department. 5. How would you detect and resolve deadlocks in SQL? 6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK. 7. Describe the ACID properties in database transactions and their significance. 8. Write a query to calculate a running total with partitions based on specific conditions. Follow Digvijay Rajput for more.

341

21 Comments

Meesho Data Analyst interview experience (0-3) - Power BI Questions: 1. Explain the concept of context transition in DAX and provide an example. 2. How would you optimize a complex Power BI report for faster performance? 3. Describe the process of creating and using calculation groups in Power BI. 4. Explain how you would handle large datasets in Power BI without compromising performance. 5. What is a composite model in Power BI, and how can it be used effectively? 6. How does the USERELATIONSHIP function work, and when would you use it? 7. Describe how to use Power Query M language for advanced data transformations. 8. Explain the difference between CROSSFILTER and TREATAS in DAX. SQL Questions: 1. How would you optimize a slow-running query with multiple joins? 2. What is a recursive CTE, and can you provide an example of when to use it? 3. Explain the difference between clustered and non-clustered indexes and when to use each. 4. Write a query to find the second highest salary in each department. 5. How would you detect and resolve deadlocks in SQL? 6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK. 7. Describe the ACID properties in database transactions and their significance. 8. Write a query to calculate a running total with partitions based on specific conditions. Follow Digvijay Rajput for more.

267

20 Comments

Project Work Description : Analyse Sample Grocery Store Outlet data Location wise sales data(Total sales,Item Type. Avg Sales,Outlet Rateing) After that, I begin work ad-hock-requests one by one. Then, for hashtag #visualization I created Dax Funtion in hashtag #PowerBI and create dashboard for each request. ·        For this project, first connect with datasources (Excel files)and Clean data. ·        Create table relationships and data models ·        Create measures and columns usings DAX formulas for analysis of various data ·        Design interactive dashboards using various visualisation. Git hub Link: https://lnkd.in/dGExEY-x

10

🚀 SQL Challenge - Day 5 Meesho SQL Interview Question PROBLEM STATEMENT: Given table contains reported covid cases in 2020.  Calculate the percentage increase in covid cases each month versus cumulative cases as of the prior month. Return the month number, and the percentage increase rounded to one decimal. Order the result by the month. Solution:- with cte as ( select  extract(month from dates) as month, sum(cases_reported) cases from covid_cases group by 1) ,cte2 as (select month, cases, sum(cases)over(order by month) as cumulative_cases from cte order by 1) select month, cases, cumulative_cases,  round((cases / lag(cumulative_cases,1) over(order by month)) * 100, 2) as percentage from cte2 #DataAnalysis #Meesho #Interview #SQL #SQLChallenge

9