PricewaterhouseCoopers Data Analyst Interview Guide

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. For removal, discuss ROW_NUMBER() window function with DELETE, or CREATE TABLE AS SELECT DISTINCT. Cover handling partial duplicates and maintaining data integrity.

INNER returns matching records, LEFT keeps all left table records, FULL keeps all records from both. Use examples with customers and orders. Discuss NULL handling and performance implications of each join type.

Use window functions (LAG) or self-join to compare current month to previous. Calculate percentage change formula. Discuss handling missing months, date truncation, and presenting results with ROUND for readability.

Use EXPLAIN to analyze query plan. Add indexes on filtered/joined columns, avoid SELECT *, use WHERE before GROUP BY, consider partitioning, and limit result sets. Discuss materialized views for complex aggregations and query caching strategies.

WHERE filters before aggregation (row-level), HAVING filters after aggregation (group-level). Example - WHERE for individual transactions, HAVING for groups with SUM > threshold. Show understanding of execution order in SQL.

Use subquery with NOT IN or LEFT JOIN with NULL check. Discuss anti-join pattern, date range filtering, and performance considerations with large datasets. Cover alternative approaches like NOT EXISTS.

Start with data validation (check tracking, data pipeline). Segment by dimension (device, channel, geography, time). Check for external factors (holidays, campaigns, site changes). Use time-series analysis and compare to historical patterns. Present findings with visualizations.

Define success criteria upfront (adoption rate, engagement, retention impact, revenue). Use funnel analysis for activation, cohort analysis for retention, and A/B testing for causation. Discuss leading vs lagging indicators and how metrics evolve over feature lifecycle.

Discuss statistical methods (Z-score, IQR), visualization (box plots, scatter plots), and domain knowledge. Cover handling outliers - remove, cap, transform, or investigate. Explain when outliers are errors vs valuable insights.

Correlation measures association, causation means one causes the other. Establish causality through A/B testing, natural experiments, regression with controls, or time-lagged analysis. Give examples of spurious correlations and confounding variables.

Start with stakeholder needs and decision-making workflows. Follow principles - clear hierarchy, actionable metrics, minimal ink-to-data ratio, consistent design. Include trends, comparisons, and drill-down capability. Discuss tools (Tableau, Power BI, Looker) and update frequency.

Statistical significance means result unlikely due to chance. Use p-value < 0.05 threshold (or 0.01 for stricter), calculate using t-test, chi-square, or regression. Discuss sample size requirements, Type I/II errors, and difference between statistical vs practical significance.

Mention VLOOKUP/XLOOKUP, SUMIFS, pivot tables, conditional formatting, COUNTIFS, INDEX/MATCH, text functions (LEFT, RIGHT, CONCAT), and date functions. Give specific use cases. Show understanding of array formulas and Power Query for advanced analysis.

Discuss tool experience (data connections, calculated fields, filters). For sales dashboard - include revenue trends, top products/regions, quota attainment, sales funnel. Use KPI cards, line charts for trends, heatmaps for segments. Cover interactivity and drill-downs.

Show understanding of row/column/filter fields, aggregation functions (SUM, COUNT, AVERAGE), calculated fields, and grouping (date rollup). Discuss slicers for interactivity, pivot charts for visualization, and refreshing data sources.

Calculated field operates row-level (like Excel column formula), calculated measure aggregates data (like SUM, AVG). Example - calculated field for profit margin per row, measure for total profit. Discuss performance implications and when to use each.

Discuss data connectors, ETL process, data blending vs joins, common keys for relationships, and data refresh schedules. Cover data modeling (star schema), handling different grain levels, and maintaining data integrity across sources.

Confidence interval is range likely to contain true population parameter. 95% CI means if we repeated sampling 100 times, 95 intervals would contain true value. Give example - revenue is $100K ± $10K. Discuss relationship to sample size and standard error.

Randomly assign users to control (A) and treatment (B), measure key metric. Calculate required sample size with power analysis. Run until statistical significance achieved. Discuss randomization, avoiding peeking, handling multiple variants, and interpreting results with confidence intervals.

Understand why data is missing (MCAR, MAR, MNAR). Options - deletion (listwise, pairwise), imputation (mean, median, regression, KNN), or flagging with indicator variable. Discuss impact on bias and when each method is appropriate.

Regression models relationship between dependent variable and independent variables. Use for prediction, identifying drivers, or testing hypotheses. Discuss simple vs multiple regression, assumptions (linearity, independence, normality), R-squared interpretation, and limitations.

Start with business impact, use simple language, focus on "so what," employ visualizations, provide context with comparisons, and offer clear recommendations. Avoid jargon. Use the "pyramid principle" - conclusion first, then supporting evidence.

Use STAR method. Quantify impact (revenue, cost savings, efficiency gains). Show how you translated data insights into actionable recommendations. Discuss stakeholder management, overcoming objections with data, and following up on implementation.

Assess business impact, urgency, effort required, and strategic alignment. Communicate transparently about timelines, set expectations, and negotiate scope. Use frameworks like impact/effort matrix. Show you understand stakeholder needs and organizational goals.

Present data objectively without confrontation, acknowledge their perspective, check data quality together, explore alternative explanations, and focus on business impact. Show humility and willingness to be wrong. Document methodology for transparency.

Calculate (Revenue from Campaign - Campaign Cost) / Campaign Cost. Discuss attribution challenges, incrementality testing (comparing to control group), considering customer lifetime value, and separating correlation from causation. Cover time horizons for different campaign types.

Mention SQL (advanced), Excel (expert), Python/R (if applicable), Tableau/Power BI, Google Analytics. Be honest about proficiency levels. Give examples of projects where you used each tool and what you accomplished.

Validate data sources, check for duplicates/nulls, use data profiling, implement automated checks, cross-reference with known benchmarks, document assumptions, and peer review analysis. Discuss ETL validation and maintaining data dictionaries.

Extract from sources, Transform (clean, aggregate, join), Load to warehouse. Discuss scheduling (Airflow, cron), error handling, incremental vs full loads, data validation checkpoints, and monitoring. Cover considerations for scalability and data freshness.

Define success metrics (CTR, conversion rate, ROAS, Quality Score). Analyze by segment (device, geography, keyword). Test ad copy, landing pages, bidding strategies. Use attribution modeling to understand customer journey. Discuss Google Ads interface and optimization recommendations.

Track watch time, completion rate, session duration, return rate by content type/creator. Segment by user cohorts, device, geography. Use time-series analysis for trends, cohort analysis for retention. Present with line charts, heatmaps, and recommendations for content strategy.

Measure click-through rate, conversion rate, revenue per recommendation, and diversity. Compare recommended vs non-recommended product performance. Use A/B testing to measure incremental impact. Discuss personalization effectiveness across customer segments and feedback loops.

Define engagement metrics (time spent, interactions, DAU/MAU). Use pre-post comparison with control group, time-series analysis, and segmentation by user type. Consider network effects and spillover. Measure both intended outcomes and unintended consequences (content distribution shifts).

Structure around market opportunity, regulatory landscape, investment requirements, and transition planning. Consider both the financial case and the strategic imperative. PwC values practical, phased approaches that balance ambition with execution reality.

This directly tests PwC's core value. Choose a genuine ethical dilemma where you chose the right path despite pressure. Show your reasoning process and how you navigated competing interests while maintaining your principles.

PwC has deep expertise in risk and compliance. Structure around current state assessment, regulatory requirements mapping, technology enablement, and organizational governance. Consider automation opportunities and the balance between compliance cost and risk mitigation.

Show your ability to bridge differences, find common ground, and leverage diverse perspectives. PwC's "Work Together" value means demonstrating genuine collaboration, not just tolerance of differences.

Decompose by revenue drivers — traffic and average ticket — then explore competitive dynamics, consumer trends, operational issues, and brand perception. PwC values thorough analysis before jumping to recommendations.

Show your learning agility and resourcefulness. PwC values people who can "Reimagine the Possible" — demonstrate how you quickly acquired new knowledge and applied it effectively under time pressure.

Segment by client type (large corporates, mid-market, SMEs, individuals), by service type (compliance, planning, disputes), and by provider type. Show awareness of the Big Four's dominant market position and regulatory drivers of demand.

Consider resilience vs. efficiency trade-offs, nearshoring opportunities, digital supply chain visibility, inventory strategy, and supplier diversification. PwC values balanced recommendations that account for both short-term actions and long-term strategic positioning.

Connect your values and career goals to PwC's purpose and culture. Reference specific PwC initiatives, thought leadership, or conversations with PwC professionals. Show you understand how PwC differentiates within the Big Four landscape.

This tests "Make a Difference." Show proactive thinking, the ability to identify opportunities beyond your immediate scope, and the initiative to drive change. Quantify the impact of your contribution.