NSDL e-Governance Data Analyst Interview: Rounds, Questions and Preparation

What the job involves

Data Analysts at Indian unicorns and high-growth startups work with large, messy datasets to uncover trends, build dashboards, and support product, growth, and operations decisions. They write SQL queries, automate reports, and present insights to non-technical stakeholders. In e-governance and fintech-like environments, they often handle high-volume transaction or citizen data, ensuring accuracy and compliance.

Role and Technical Knowledge

1. 1

   What is the difference between a primary key and a foreign key in a database? Give an example from a transaction system.

   Explain uniqueness vs referential integrity. Use a payments table with transaction_id as primary key and user_id as foreign key linking to users.

2. 2

   How would you write a SQL query to find the second highest transaction amount per user in a fintech app?

   Use a window function like ROW_NUMBER() or DENSE_RANK() partitioned by user, ordered by amount descending, then filter rank = 2.

3. 3

   Explain the difference between WHERE and HAVING in SQL. When would you use each?

   WHERE filters rows before aggregation, HAVING filters after GROUP BY. Use HAVING to filter aggregated results like total transaction count > 10.

4. 4

   What are some common data quality issues you might see in citizen or transaction data, and how would you handle them?

   Mention missing values, duplicates, inconsistent formats, outliers. Suggest profiling, validation rules, and cleaning steps before analysis.

Practical Scenarios and Problem Solving

1. 5

   You are given a dataset of daily active users for a government service app. How would you identify a sudden drop in engagement?

   Break down by date, region, platform, and user cohort. Check for data pipeline issues, app updates, or external events. Use moving averages and anomaly detection.

2. 6

   A product manager asks you to measure the success of a new feature that allows users to link multiple bank accounts. What metrics would you track?

   Define adoption rate, time to link, success rate, retention, and impact on transaction volume. Suggest A/B test if possible.

3. 7

   How would you clean a dataset where 20 percent of the 'date of birth' column is missing for a KYC analysis?

   Assess if missingness is random. Consider imputation, flagging, or excluding. Discuss impact on analysis and document assumptions.

4. 8

   You need to present a weekly report on failed transactions to non-technical stakeholders. How would you structure it?

   Start with headline numbers, then trends, root causes, and recommendations. Use simple visuals and avoid jargon. Highlight actionable insights.

Behavioural and HR Questions

1. 9

   Tell me about a time you worked with a messy dataset. How did you ensure accuracy?

   Use STAR method. Describe the dataset, your cleaning steps, validation checks, and the outcome. Show attention to detail.

2. 10

   Why do you want to work as a Data Analyst in a high-growth startup or e-governance company?

   Connect personal interest in data-driven decisions with the sector's scale and impact. Mention eagerness to learn and handle large datasets.

3. 11

   Describe a situation where you had to explain a technical finding to someone with no data background.

   Show empathy and clarity. Explain how you simplified the concept, used analogies or visuals, and confirmed understanding.

4. 12

   How do you prioritise multiple data requests from different teams?

   Discuss impact vs effort, deadlines, and stakeholder communication. Give an example of using a simple prioritisation framework.

Tools and Technical Depth

1. 13

   What is the difference between a JOIN and a UNION in SQL? When would you use each?

   JOIN combines columns from related tables, UNION combines rows from similar tables. Use JOIN for enriching data, UNION for appending datasets.

2. 14

   How would you use Excel or Google Sheets to create a pivot table showing monthly active users by city?

   Describe dragging date to rows, city to columns, user ID to values with count. Mention grouping dates by month and filtering.

3. 15

   Write a Python or R snippet to calculate the average transaction amount per user from a CSV file.

   Use pandas groupby in Python or dplyr in R. Show reading file, grouping by user, and computing mean. Mention handling missing values.

4. 16

   What is a dashboard, and what are three key elements you would include in a dashboard for monitoring daily payment success rates?

   Define dashboard as visual summary. Include success rate trend, failure reasons breakdown, and comparison to previous period. Mention filters and refresh frequency.