Shell Data Analyst Interview question and answer December 2024

Shell Data Analyst Interview Experience: CTC - 18 LPA

Shell’s Data Analyst role demands strong SQL, Python, and Power BI skills alongside the ability to align technical insights with business strategy. Below, I’ve shared the questions asked during my interview process and how I would have answered them.

---

SQL Questions

1️⃣ Write a query to calculate the cumulative revenue per customer for each month in the last year.

• Answer:

  sql
  
  SELECT CustomerID,  
         DATE_FORMAT(Date, '%Y-%m') AS Month,  
         SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY DATE_FORMAT(Date, '%Y-%m')) AS CumulativeRevenue  
  FROM Transactions  
  WHERE Date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);  
  

---

2️⃣ Identify plants that consistently exceeded their daily average output for at least 20 days in a given month.

• Answer:

  sql
  
  WITH DailyAvg AS (  
      SELECT PlantID,  
             AVG(Output) AS AvgOutput  
      FROM Production  
      GROUP BY PlantID  
  ),  
  ExceedDays AS (  
      SELECT p.PlantID, DATE(p.Date) AS Day,  
             COUNT(*) OVER (PARTITION BY p.PlantID) AS ExceedCount  
      FROM Production p  
      JOIN DailyAvg da ON p.PlantID = da.PlantID  
      WHERE p.Output > da.AvgOutput  
  )  
  SELECT PlantID  
  FROM ExceedDays  
  WHERE ExceedCount >= 20;  
  

---

3️⃣ Find employees with the highest consecutive absences in the last quarter.

• Answer:

  sql
  
  WITH AbsenceRank AS (  
      SELECT EmployeeID, Date,  
             ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Date)  
             - ROW_NUMBER() OVER (ORDER BY Date) AS RankGroup  
      FROM EmployeeAttendance  
      WHERE Status = 'Absent' AND Date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  
  )  
  SELECT EmployeeID, COUNT(*) AS ConsecutiveAbsences  
  FROM AbsenceRank  
  GROUP BY EmployeeID, RankGroup  
  ORDER BY ConsecutiveAbsences DESC  
  LIMIT 1;  
  

---

4️⃣ Pros and cons of using indexes in SQL, and when would you avoid using them?

• Answer:
  Pros: Speeds up query performance, especially on large datasets.
  Cons: Slows down INSERT/UPDATE operations and increases storage requirements.
  Avoid: When tables experience frequent writes or have low query volume.

---

5️⃣ Differences between window and aggregate functions with examples.

• Answer:
  Window functions operate on a subset of rows and return a result for each row, whereas aggregate functions collapse rows into a single value.
  • Window Function Example: Cumulative sales for each customer.

    sql
    SELECT CustomerID, SUM(Sales) OVER (PARTITION BY CustomerID ORDER BY Date) AS CumulativeSales  
    FROM Orders;  

  • Aggregate Function Example: Total sales per customer.

    sql
    SELECT CustomerID, SUM(Sales) AS TotalSales  
    FROM Orders  
    GROUP BY CustomerID;  
    

---

Python Questions

6️⃣ Merge multiple CSV files and clean the data.

• Answer:

  python
  
  import pandas as pd  
  import os  
  
  def merge_csv(directory):  
      all_files = [f for f in os.listdir(directory) if f.endswith('.csv')]  
      dataframes = [pd.read_csv(os.path.join(directory, file)) for file in all_files]  
      merged_df = pd.concat(dataframes)  
  
      # Basic cleaning  
      merged_df.drop_duplicates(inplace=True)  
      merged_df.fillna(0, inplace=True)  
  
      merged_df.to_csv('merged_file.csv', index=False)  
  
  merge_csv('path_to_directory')  
  

---

7️⃣ Group a list of dictionaries by a key and calculate summary statistics.

• Answer:

  python
  
  from collections import defaultdict  
  
  def group_data(data, key):  
      grouped = defaultdict(list)  
      for item in data:  
          grouped[item[key]].append(item)  
  
      summary = {k: len(v) for k, v in grouped.items()}  
      return summary  
  
  data = [{'Category': 'A', 'Value': 10}, {'Category': 'B', 'Value': 20}, {'Category': 'A', 'Value': 15}]  
  print(group_data(data, 'Category'))  
  

---

8️⃣ Difference between list, tuple, and dictionary with examples.

• Answer:
  • List: Mutable, ordered collection (e.g., [1, 2, 3]).
  • Tuple: Immutable, ordered collection (e.g., (1, 2, 3)).
  • Dictionary: Key-value pairs, unordered (e.g., {'key': 'value'}).

---

9️⃣ Automate the generation of monthly reports from an Excel dataset.

• Answer:

  python
  
  import pandas as pd  
  
  def generate_reports(file_path):  
      data = pd.read_excel(file_path)  
      grouped = data.groupby('Month')  
  
      for month, group in grouped:  
          group.to_excel(f'{month}_report.xlsx', index=False)  
  
  generate_reports('sales_data.xlsx')  
  

---

Power BI Questions

🔟 Create a dashboard to track production plant efficiency.

• Use measures like OEE (Overall Equipment Efficiency), visualize KPIs with cards, and use line graphs for trends.

        1️⃣ Handle data source refresh delays.

• Optimize queries, use DirectQuery mode, and ensure a reliable connection.

        2️⃣ Row-level vs. role-level security.

• Row-level: Controls data access at the row level for individual users.
• Role-level: Groups users into roles to apply security policies collectively.

        3️⃣ Visualize trends and outliers in daily sales data.

• Use scatter plots and line charts with dynamic filters to highlight anomalies.

        4️⃣ Create a calculated measure for YoY growth.
DAX YoY Growth = (SUM(Sales) - CALCULATE(SUM(Sales), SAMEPERIODLASTYEAR(Date))) / CALCULATE(SUM(Sales), SAMEPERIODLASTYEAR(Date))

---

General Questions

        5️⃣ Data-driven insights example.

• At my previous role, I analyzed customer purchase patterns and introduced a discount strategy that increased sales by 15%.

        6️⃣ Prioritizing tasks in high-pressure environments.

• Use tools like Eisenhower Matrix and regularly communicate with stakeholders to manage expectations.

        7️⃣ Why join Shell?

• Shell’s commitment to sustainability aligns with my values. My expertise in SQL, Python, and BI tools will help drive data-driven decision-making in Shell’s operational efficiency goals.

---

Pro Tip

Stay confident, structure your answers, and align them with the business impact wherever possible.

---

#Shell #DataAnalyst #InterviewExperience #SQL #Python #PowerBI