Meta Data Analyst Interview Preparation Guide - Staff Level

Data Analyst

Interview Rounds

Recruiter Screening

45 min4 focus topicsculture fit

What to Expect

The initial 30-45 minute call with a Meta recruiter to assess overall fit, background, and interest in the role. The recruiter explores your analytics experience, career progression, and reasons for joining Meta. They evaluate communication clarity, curiosity, ability to explain complex work accessibly, and alignment with Meta's mission to connect people globally. For Staff level candidates, recruiters assess your track record of impact, leadership experience, readiness for senior responsibilities, and strategic thinking about analytics.

Tips & Advice

Develop a compelling career narrative highlighting 2-3 high-impact analytical initiatives where you drove decisions and demonstrated leadership. Use a Goal → Metrics → Outcomes structure when discussing projects. Emphasize measurable business impact and your role in influencing cross-functional decisions. For Staff level, articulate your mentorship philosophy and examples of junior analysts you've developed into strong contributors. Show genuine enthusiasm for Meta's mission and specific products. Prepare thoughtful questions about the team's current challenges, analytics priorities, and growth opportunities. Research Meta's recent product launches and understand core business metrics. Practice explaining technical concepts in accessible language suitable for non-technical audiences.

Focus Topics

Meta Mission and Product Ecosystem Understanding

Demonstrate familiarity with Meta's mission of connecting people globally, core products (Facebook, Instagram, WhatsApp, Threads), business model, and analytics challenges. Show understanding of user metrics and engagement frameworks.

Practice Interview

Study Questions

Mentorship and Team Development Philosophy

Share your approach to developing junior analysts, fostering growth, and building high-performing teams. Provide concrete examples of analysts you've mentored and their career progression.

Practice Interview

Study Questions

Career Narrative and Leadership Track Record

Articulate your analytics career progression emphasizing high-impact projects, demonstrated leadership, and influence on product decisions. Highlight complexity of problems you've solved and scale of impact.

Practice Interview

Study Questions

Clear Communication and Influence

Practice explaining complex analytical methodologies, findings, and strategic implications in clear, compelling language for diverse audiences including executives, product managers, and engineers.

Practice Interview

Study Questions

Technical Phone Screen - SQL and Analytics

60 min5 focus topicstechnical

What to Expect

A 45-60 minute technical phone screen conducted remotely focusing on SQL proficiency and analytical problem-solving. You'll solve 1-2 moderately complex SQL problems that reflect real-world analytics scenarios at Meta. Problems typically involve working with multi-table datasets, performing aggregations, joining tables, and deriving business insights. The interviewer evaluates query correctness, code clarity, optimization thinking, communication of your approach, and ability to navigate ambiguous problem definitions. For Staff level candidates, expect sophisticated scenarios involving data modeling decisions, performance optimization challenges, or complex business logic requiring strategic thinking about analytical approach.

Tips & Advice

Master advanced SQL concepts including window functions (ROW_NUMBER, RANK, LAG/LEAD, aggregates), Common Table Expressions (CTEs), complex joins, subqueries, and query optimization techniques. When approaching problems, think out loud and explain your reasoning before writing code. Start by asking clarifying questions about data structure, scale, and edge cases. Write clean, well-commented SQL that demonstrates your thought process. For Staff level, show sophisticated approaches to ambiguity by discussing multiple solution approaches, making explicit assumptions, and explaining tradeoffs between simplicity and optimization. Validate your approach with the interviewer before implementation. Practice explaining optimization decisions and their business implications. Be prepared to discuss performance implications at scale and how you'd monitor query efficiency in production. Focus on real product analytics problems like engagement metrics, retention analysis, funnel analysis, cohort retention, and user lifecycle metrics.

Focus Topics

Data Modeling and Schema Design Tradeoffs

Understand relational data structures, normalization vs. denormalization tradeoffs, and how data modeling choices impact query efficiency and analytical clarity.

Practice Interview

Study Questions

Problem Definition and Ambiguity Navigation

Approach open-ended problems by asking clarifying questions, making explicit assumptions, designing robust solutions, and discussing edge cases and data validation approaches.

Practice Interview

Study Questions

Advanced SQL Patterns and Window Functions

Master complex SQL patterns including window functions for ranking and aggregation, CTEs for code organization, recursive queries, and self-joins for complex transformations.

Practice Interview

Study Questions

Meta Core Product Metrics

Practice solving problems related to core Meta metrics: Daily Active Users (DAU), Monthly Active Users (MAU), engagement rates, content interactions, retention cohorts, and user lifecycle analysis.

Practice Interview

Study Questions

Query Optimization and Performance

Discuss query performance at scale including indexing strategies, query plan analysis, and optimization techniques. Explain tradeoffs between readability and execution efficiency.

Practice Interview

Study Questions

On-site Round 1: Technical SQL Interview

45 min5 focus topicstechnical

What to Expect

A 30-45 minute in-depth technical interview conducted by a Meta senior analyst or engineer at the on-site location. You'll solve complex SQL problems that mirror real analytics challenges faced at Meta. Problems may involve designing queries for dashboards, handling data quality issues, optimizing complex analytical queries, or solving multi-step analytical challenges. The interviewer evaluates technical depth, ability to structure complex problems systematically, optimization thinking, code quality, and clear communication of technical decisions. For Staff level candidates, expect sophisticated scenarios involving cross-functional data contexts, performance constraints at scale, architectural decisions about data pipelines, or complex business logic.

Tips & Advice

Approach this as a collaborative technical discussion rather than a test. Think out loud, ask clarifying questions about data structure, scale expectations, and business context before diving into implementation. Start with a clear approach and discuss it with the interviewer. Write clean, well-structured SQL with meaningful variable names and comments explaining your logic. Be prepared to discuss and implement optimizations based on feedback. For Staff level, demonstrate sophisticated technical leadership: Identify multiple solution approaches and discuss tradeoffs, proactively raise potential edge cases and data quality considerations, think about scalability and maintainability, and guide the interviewer through your thinking. Show comfort making technical decisions and defending them with clear reasoning. If you encounter issues, talk through debugging process and ask for hints rather than getting stuck silently.

Focus Topics

Query Efficiency and Optimization

Optimize queries for performance considering execution plans, indexing strategies, and computational efficiency. Discuss tradeoffs between query complexity and readability.

Practice Interview

Study Questions

Data Quality and Validation Techniques

Proactively identify potential data quality issues in queries including NULL handling strategies, duplicate detection, data type validation, and implementing quality checks.

Practice Interview

Study Questions

SQL Code Quality and Best Practices

Write readable, maintainable SQL code with clear naming conventions, logical organization, helpful comments, and consistent formatting that serves as examples for junior analysts.

Practice Interview

Study Questions

Collaborative Technical Problem-Solving

Engage the interviewer in technical discussions, ask clarifying questions, incorporate feedback, discuss alternative approaches, and show flexibility in adjusting strategies.

Practice Interview

Study Questions

Complex Multi-Step Query Design

Design and implement complex queries involving multiple aggregations, window functions, and conditional logic. Break down multi-step problems into manageable components using CTEs.

Practice Interview

Study Questions

On-site Round 2: Analytics and Product Sense

45 min6 focus topicscase study

What to Expect

A 30-45 minute interview focused on analytical thinking, product sense, and business judgment. You'll receive open-ended case studies or scenario-based questions about analyzing data to drive product decisions. Scenarios might include: identifying engagement trends, analyzing retention problems, designing metrics frameworks, investigating anomalies, proposing experiments, or building dashboards for stakeholders. The interviewer evaluates ability to structure ambiguous problems, think strategically about metrics and KPIs, propose testable hypotheses, recommend data-driven actions, and connect insights to product strategy. For Staff level candidates, emphasis on strategic thinking, cross-functional considerations, ability to influence high-level decisions, and mentorship of analytical thinking.

Tips & Advice

Use a structured analytical approach: (1) Clarify the problem and business context, (2) Define success metrics and what good looks like, (3) Propose analytical approach and key questions to investigate, (4) Discuss potential insights and hypotheses, (5) Recommend actions and next steps. For Staff level, demonstrate strategic thinking by connecting data insights to product decisions and business impact. Ask insightful clarifying questions about context, constraints, and stakeholder perspectives. Make assumptions explicit and discuss their implications. Think cross-functionally about how product, engineering, and business teams would be involved. Propose multiple hypotheses rather than anchoring on one interpretation. For Staff level, show how you'd mentor others through this analysis and your framework for building analytical capabilities on your team. Discuss how you'd communicate findings to executives and drive alignment around data-driven decisions.

Focus Topics

Experimental Design and Testing

Understand experiment design basics, hypothesis formulation, statistical significance, sample size considerations, and interpreting experimental results.

Practice Interview

Study Questions

Hypothesis-Driven Analytical Reasoning

Approach problems by generating multiple testable hypotheses, designing efficient analysis to test them, and drawing data-backed conclusions. Discuss validation approaches.

Practice Interview

Study Questions

Metric Design and Hierarchies

Design appropriate success metrics for analytical scenarios. Understand leading vs. lagging indicators, metric composition, guardrail metrics, and north star metric concepts.

Practice Interview

Study Questions

Core Meta Metrics and Frameworks

Deep understanding of Meta's primary metrics: Daily/Monthly Active Users (DAU/MAU), engagement rates (likes, comments, shares per user), retention curves, cohort analysis, monetization metrics, content quality signals.

Practice Interview

Study Questions

Product Analytics and Strategy Thinking

Connect analytical insights to product decisions, user behavior understanding, and Meta's strategic priorities. Think about product roadmap implications and business impact.

Practice Interview

Study Questions

Problem Structuring and Ambiguity

Take vague, open-ended problems and structure them into analyzable questions. Make assumptions explicit, discuss impact, and handle incomplete information gracefully.

Practice Interview

Study Questions

On-site Round 3: Behavioral - Leadership and Impact

45 min5 focus topicsbehavioral

What to Expect

A 30-45 minute behavioral interview conducted by a hiring manager or senior team member. You'll discuss past experiences, accomplishments, challenges overcome, and leadership approach. Meta uses the STAR method (Situation, Task, Action, Result) to structure conversation. For Staff level candidates, special emphasis on: track record of high-impact initiatives, mentoring and developing junior analysts, influencing cross-functional decisions, leading through influence without authority, handling complexity and ambiguity, and contribution to team culture and excellence.

Tips & Advice

Prepare 3-4 compelling STAR stories demonstrating: (1) Mentoring junior team members and their career growth, (2) Leading a high-impact analytical project from conception through impact, (3) Collaborating across functions to drive important decisions, (4) Handling ambiguity or setback and lessons learned. For Staff level, emphasize your leadership philosophy, approach to building teams, and examples of how you've created impact beyond your individual contributions. Share specific examples of mentees you've developed and their progression. Discuss how you influence decisions through data and relationship building. Show humility about failures and growth mindset. When discussing accomplishments, emphasize team effort and how you enabled others to succeed. Be authentic and specific with numbers and outcomes. Connect stories to Meta's values.

Focus Topics

Navigating Ambiguity and Complexity

Describe situations with incomplete information, conflicting priorities, or technical challenges. Show how you structured problems, made decisions, and what you learned.

Practice Interview

Study Questions

Meta Cultural Values and Alignment

Demonstrate alignment with Meta's values: Move Fast (execution and velocity), Focus on Impact (results orientation), Build Awesome Things (quality and ambition), Be Direct and Honest (honest communication). Share relevant examples.

Practice Interview

Study Questions

Leadership of High-Impact Initiatives

Describe complex analytical projects you led end-to-end: from problem definition through implementation and demonstrating business impact. Show ownership and initiative.

Practice Interview

Study Questions

Mentorship and Analytical Talent Development

Provide specific examples of mentoring junior analysts: how you identified gaps, provided guidance, accelerated their growth, and how they've progressed in their careers.

Practice Interview

Study Questions

Cross-Functional Influence and Partnership

Share examples of influencing product decisions, working with product managers and engineers, building stakeholder support, and driving alignment around data insights.

Practice Interview

Study Questions

On-site Round 4: Behavioral - Collaboration and Team Fit

45 min5 focus topicsbehavioral

What to Expect

A 30-45 minute behavioral interview with another team member (could be peer, adjacent manager, or cross-functional collaborator) assessing collaborative style, working approach, team contribution, and overall fit. Discussion focuses on how you work day-to-day with colleagues, approach collaboration, build relationships, handle disagreements, and contribute to team success. For Staff level candidates, emphasis on: collaborative leadership, building psychological safety, driving team excellence, communication and influence skills, and your impact on team dynamics and culture.

Tips & Advice

This second behavioral round often explores day-to-day collaboration and working style. Prepare stories showing: successful partnership with difficult stakeholders or conflict resolution, building alignment across disagreement, your communication approach, examples of improving team processes or culture, and how you balance independence with collaboration. For Staff level, discuss your leadership presence and how you create psychological safety where people feel comfortable sharing ideas and making mistakes. Share examples of how you've retained strong talent, built trust with team members, and created positive team culture. Discuss your philosophy on difficult conversations and feedback. Be authentic about your working style while showing flexibility and adaptability. Emphasize what you contribute to team success beyond individual work.

Focus Topics

Team Culture and Excellence

Describe your approach to creating high-performing teams, fostering continuous learning, maintaining quality standards, and building team culture of excellence.

Practice Interview

Study Questions

Building Scalable Analytical Systems

Share examples of designing automated reporting systems, dashboards, or analytical infrastructure that reduce manual work and improve team efficiency.

Practice Interview

Study Questions

Collaboration and Conflict Resolution

Share examples of resolving disagreements constructively, building consensus, handling difficult interpersonal situations professionally, and learning from challenges.

Practice Interview

Study Questions

Communication and Presenting Insights

Share examples of translating analytical findings into compelling narratives for different audiences. Show ability to adjust communication style for executives, technical teams, or business stakeholders.

Practice Interview

Study Questions

Stakeholder Collaboration and Influence

Demonstrate ability to work effectively with diverse stakeholders (product managers, engineers, executives, business partners), understand their needs, and influence decisions.

Practice Interview

Study Questions

Frequently Asked Data Analyst Interview Questions

Query Optimization and Execution PlansMediumTechnical

79 practiced

Describe how you'd approach diagnosing a slow ETL extract query that joins 6 tables and occasionally times out under nightly load. Include steps for isolating the slow operators, capturing execution metrics under load, and strategies like indexing, pre-aggregation, partitioning, or moving heavy computation to a worker cluster.

Sample Answer

Situation: Nightly ETL extract that joins 6 tables intermittently times out under peak load. As the data analyst owning the extract, I'd diagnose and fix it so reports remain reliable.

Approach — stepwise:

1. Reproduce & baseline- Run the extract during a controlled load window to reproduce timeout.- Capture current run time, timeout frequency, and row counts.

2. Isolate slow operators- Get the query plan with runtime metrics (example for PostgreSQL):

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <your_query>;

- Look for heavy operators: nested loop scans, large sequential scans, or expensive sort/hash builds. Note which join or aggregation consumes most time/memory.

3. Capture execution metrics under load- While job runs under normal nightly load, collect: - Database activity: pg_stat_activity, pg_stat_statements (Postgres), or equivalent. - System metrics: CPU, memory, I/O, network. - Query-level timings: EXPLAIN ANALYZE or enable extended logging for slow_query_log.- Correlate peaks with operator hotspots (e.g., 90% time in hash join build).

4. Short-term mitigations- Add targeted indexes on join/filter columns (covering indexes if possible).- Force a better join order with rewritten predicates or optimizer hints only if safe.- Break the query into smaller steps and use temporary tables to persist intermediate results:

sql

CREATE TEMP TABLE t_intermediate AS
SELECT ... FROM small_tables JOIN ...
;
ANALYZE t_intermediate;
-- then join with big tables

5. Medium/long-term strategies- Partition large fact tables by date to prune partitions at query time.- Pre-aggregate frequently-used metrics into materialized views refreshed nightly.- Move heavy transformations to an upstream worker cluster (Spark/DBT) to produce denormalized tables the reports can query efficiently.- Consider increasing resource allocation for the nightly window (work_mem, parallel_workers).

6. Validation & monitoring- Re-run EXPLAIN ANALYZE to confirm operator time reduced.- Add alerts for query durations and row-count anomalies.- Document changes and schedule periodic review.

Why this works:- Stepwise isolation (EXPLAIN + metrics) finds the real bottleneck; targeted indexing/partitioning reduces I/O; pre-aggregation or worker clusters shift CPU work off the OLTP DB, making extracts more reliable under load. Collaboration with engineering/DBA for permissions and schema changes is essential.

SQL Fundamentals and Query WritingHardTechnical

46 practiced

Explain why SQL result ordering is non-deterministic without ORDER BY. Give an example where using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY some_metric) without a stable tie-breaker produces non-repeatable results. Show how to make ordering deterministic in such a case.

Sample Answer

SQL does not guarantee any row order unless you specify ORDER BY. The query planner can return rows in any sequence depending on indexes, join strategies, parallel execution, or planner optimizations — so results are non-deterministic across runs or engine versions.

Example showing non-repeatable ROW_NUMBER when tie-breaker is missing:

sql

-- Suppose many events have identical score; no deterministic tie-breaker
WITH ranked AS (
  SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY cohort ORDER BY score DESC) AS rn
  FROM events
  WHERE cohort = '2025-Q1'
)
SELECT * FROM ranked WHERE rn = 1;

If multiple rows share the same top score for a cohort, the ORDER BY score DESC alone doesn't define order among ties. Different executions (or parallelism) may choose different tied rows as rn = 1.

Make ordering deterministic by adding a stable tie-breaker (unique, immutable column):

sql

WITH ranked AS (
  SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (
      PARTITION BY cohort
      ORDER BY score DESC, user_id ASC  -- user_id is unique and stable
    ) AS rn
  FROM events
  WHERE cohort = '2025-Q1'
)
SELECT * FROM ranked WHERE rn = 1;

Best practices:- Always include ORDER BY when you need repeatable order.- Use a deterministic tie-breaker (primary key, timestamp, or composite) in window functions.- For reporting, persist ordering logic in ETL or views so downstream consumers get stable results.

Data Storytelling and Insight CommunicationMediumTechnical

78 practiced

You analyzed retention and found it improved after a promotion. Prepare a 5-bullet executive summary that states the finding (magnitude and timeframe), your confidence level, possible alternative explanations, and two follow-up analyses that would increase confidence in attribution.

Advanced Querying with Structured Query LanguageEasyTechnical

25 practiced

Given table events(user_id bigint, event_type varchar, occurred_at timestamp), write two PostgreSQL queries that return the number of unique users who performed event_type = 'signup' in the last 90 days: (a) using COUNT(DISTINCT) and (b) using GROUP BY. Then explain trade-offs between the two approaches on very large datasets.

Sample Answer

(a) COUNT(DISTINCT) — single aggregate

sql

SELECT COUNT(DISTINCT user_id) AS signup_users_90d
FROM events
WHERE event_type = 'signup'
  AND occurred_at >= now() - interval '90 days';

(b) GROUP BY then count rows — two-step aggregation

sql

WITH signups AS (
  SELECT user_id
  FROM events
  WHERE event_type = 'signup'
    AND occurred_at >= now() - interval '90 days'
  GROUP BY user_id
)
SELECT COUNT(*) AS signup_users_90d
FROM signups;

Trade-offs (very large datasets):- Simplicity: COUNT(DISTINCT) is shorter and expressive.- Performance: COUNT(DISTINCT) can be memory-heavy because PostgreSQL may need to build an in-memory hash of distinct keys; on very large cardinalities it can spill to disk and be slow.- GROUP BY approach lets you control intermediate steps (e.g., partial aggregation, parallelism) and can be optimized with indexes (CREATE INDEX ON events(event_type, occurred_at, user_id)). It also makes it easier to add filters or join to other tables before final counting.- Alternatives: use approximate distinct (hyperloglog) via extension (postgresql-hll) for much faster, low-memory estimates; or maintain pre-aggregated daily unique-user tables for reporting.- Edge cases: beware of timezone in occurred_at and duplicates if same user has multiple signups — both queries handle that by deduping user_id.

Problem Definition and Hypothesis FormationEasyTechnical

40 practiced

Your product manager asks to 'improve website conversion rate'. As a data analyst, list the clarifying questions you would ask to translate this into a measurable analytics problem. Cover definitions (what counts as conversion), time window, target segments, baseline, minimum meaningful uplift, data sources and latency, event attribution, constraints (sample size, privacy/regulatory), rollout or experiment feasibility, and stakeholders. Provide at least 8 distinct questions and explain why each matters.

Problem Structuring and Analytical FrameworksEasyTechnical

61 practiced

An executive requests an 'engagement dashboard' for weekly review. List the 8–10 key components the dashboard should include (metric name, short definition, and target where applicable), describe one visualization for each component, and explain how you would design it to be actionable for non-technical stakeholders.

Sample Answer

1) Weekly Active Users (WAU) — unique users with ≥1 session that week. Target: +5% WoW. Visualization: line with week-over-week percent change band. Actionable design: highlight dips in red, annotate major campaigns; include filter by segment so execs see which cohorts drive changes.

2) Daily/Weekly Sessions — total sessions. Target: stable or growing per business plan. Visualization: bar chart (week stacked by day) with trendline. Design: show sessions per channel (color) to link to marketing activity.

3) Engagement Rate — % of sessions with meaningful action (e.g., time > 2min or key event). Target: 30%. Visualization: KPI card + small funnel chart. Design: callout if conversion from session→action drops >10% to prompt investigation.

4) Average Session Duration — mean minutes/session. Target: >3 min. Visualization: sparkline with distribution (boxplot) below. Design: combine with top pages to indicate where time is spent.

5) Retention (7-day/30-day) — % of users returning after X days. Target: 25% (7-day), 10% (30-day). Visualization: cohort retention table heatmap. Design: color-coded thresholds and simple interpretation: “Retention improving/worsening”.

6) Conversion Rate (primary CTA) — % of sessions completing key goal. Target: specific to funnel (e.g., 4%). Visualization: funnel chart with drop-off percentages at each step. Design: surface biggest leak and possible A/B test ideas.

7) Feature Usage — % of users using a target feature. Target: 20% adoption. Visualization: horizontal bar sorted by feature + trend sparkline. Design: prioritize features by adoption and impact; clickable to see user segments.

8) Churned Users — number who stopped using over period. Target: minimize; trend down. Visualization: stacked area showing new vs churned users. Design: show top reasons (if available) and action items (win-back campaigns).

9) Net Promoter Score (NPS) / Satisfaction — survey score. Target: ≥40. Visualization: gauge + distribution histogram of responses. Design: include top verbatim themes and alert when score drops.

10) Revenue per User (ARPU) or Monetization Metric — average revenue per active user. Target: growth aligned to forecasts. Visualization: combo chart (line ARPU + bars revenue). Design: show contribution by channel and flag when ARPU deviates from forecast.

General design principles: one-page executive summary with 2–3 headline KPIs at top, consistent color language (good/neutral/bad), short plain-English insight for each metric (what happened, why, recommended action), and filters for time range and segments. Keep interactions simple (predefined segments) and include data freshness and confidence notes so non-technical stakeholders can act quickly.

Query Optimization and Execution PlansMediumTechnical

94 practiced

A report uses a WHERE clause like `LOWER(email) = 'alice@example.com'` which prevents use of a btree index on email. As a data analyst, suggest at least three different ways to regain index-backed performance for lookups by normalized email, explain how each works and the trade-offs (storage, complexity, back-compat).

Sample Answer

Situation: A report filters with LOWER(email) = 'alice@example.com', which blocks a btree index on email. Here are three practical ways to restore index-backed lookups, how they work, and trade-offs.

1) Create a functional (expression) index on LOWER(email)- How: Add an index like CREATE INDEX ON users (LOWER(email));- Query benefit: The planner can use the index for WHERE LOWER(email) = '...'.- Trade-offs: Minimal storage overhead (index stores normalized values). Low complexity and no schema changes. Back-compat: none—existing queries using LOWER(...) work unchanged. DB support needed (most modern RDBMSes support expression indexes).

2) Add and index a normalized column (stored/generated)- How: Add email_normalized varchar and populate with LOWER(TRIM(email)) via ETL or a generated/stored column, then index email_normalized.- Query benefit: WHERE email_normalized = 'alice@example.com' uses a normal btree index (fast).- Trade-offs: Extra storage for the column + index. Slight complexity: ensure writes/updates keep it in sync (use generated column if DB supports it to minimize drift). Back-compat: requires changing queries/dashboards to use the new column or add view-layer compatibility.

3) Use case-insensitive datatype / collation (DB-specific)- How: Switch column to a case-insensitive collation or use a CI type (e.g., PostgreSQL citext) and index the column normally.- Query benefit: WHERE email = 'alice@example.com' becomes case-insensitive and indexable without expression wrapping.- Trade-offs: Migration effort to change column type/collation (data rewrite for large tables). Possible subtle semantics change for comparisons (affects uniqueness constraints and sorting). Back-compat: queries need no change, but application assumptions about case-sensitivity may be impacted.

Additional notes:- Verify planner uses the index (EXPLAIN). For low-selectivity lookups, consider combined indexes (email_normalized, tenant_id).- For immediate low-effort fix, functional index is fastest. For long-term robustness and easier queries, normalized column or CI type is preferable.

SQL Fundamentals and Query WritingHardTechnical

47 practiced

Write a SQL expression to mask a US SSN stored as ssn TEXT so that only the last 4 digits are visible (e.g., '***-**-1234'). Use Postgres string functions and consider NULLs and malformed inputs. Additionally, describe an approach using views or column-level policies to prevent PII leakage to analysts who should not see full SSNs.

Sample Answer

Use a defensive expression that returns NULL for NULLs, leaves already-masked values alone, and extracts last 4 digits when possible. Example Postgres expression:

sql

-- Returns '***-**-1234', NULL if ssn IS NULL, or 'INVALID' for malformed inputs
COALESCE(
  CASE
    WHEN ssn IS NULL THEN NULL
    WHEN ssn ~ '^\*{3}-\*{2}-\d{4}$' THEN ssn              -- already masked
    WHEN ssn ~ '\d{4}$' THEN '***-**-' || right(regexp_replace(ssn, '\D', '', 'g'), 4)
    ELSE 'INVALID'
  END,
  NULL
) AS ssn_masked

Explanation:- regexp_replace(ssn, '\D', '', 'g') removes non-digits so inputs like "123-45-6789" or "123456789" work.- right(...,4) picks last 4 digits.- Regex checks avoid revealing malformed or already-masked values.- COALESCE handles any unexpected NULL propagation.

Preventing PII leakage: two practical approaches1. Views + GRANTS: - Create a read-only view that exposes masked_ssn (using the expression above) and grant analysts SELECT on the view but not the base table. - Keep base table access limited to trusted roles (DBAs, compliance). - Example: CREATE VIEW analytics.customers_view AS SELECT id, name, (mask expr) AS ssn_masked, ...; GRANT SELECT ON analytics.customers_view TO analyst_role;

2. Policy-based (recommended for per-role enforcement): - Use Row-Level Security + a security-definer function or current_setting check: create a function that returns full SSN only when role has permission (or when session setting 'show_full_ssn' = 'true' set by privileged role). - Alternatively, use Postgres column-level encryption (pgcrypto) storing encrypted SSN and provide a decryption function callable only by privileged roles.

Combine: store encrypted SSNs, expose masked values in views for analysts, and allow controlled decryption for authorized users with audit logging.

Data Storytelling and Insight CommunicationEasyTechnical

146 practiced

Given the following daily transactions for the last 7 days: {2025-11-01:120, 11-02:115, 11-03:90, 11-04:95, 11-05:92, 11-06:93, 11-07:91}, write a 2-sentence executive summary that states the key finding and one immediate recommended action. Assume the audience is the growth PM and you must lead with the headline insight.

Advanced Querying with Structured Query LanguageEasyTechnical

18 practiced

You have a PostgreSQL table orders(order_id bigint PRIMARY KEY, customer_id bigint, amount numeric(10,2), status varchar, created_at timestamp). Write a PostgreSQL-compatible SQL query to return all orders with status = 'completed' placed in the last 30 days and amount > 100.00, sorted by amount descending. Mention any assumptions about timezones.

Sample Answer

Approach: filter by status, amount and created_at using a 30-day interval from the current timestamp, then sort by amount descending.

sql

SELECT order_id,
       customer_id,
       amount,
       status,
       created_at
FROM orders
WHERE status = 'completed'
  AND amount > 100.00
  AND created_at >= now() - INTERVAL '30 days'
ORDER BY amount DESC;

Key notes and assumptions:- This uses now() (equivalent to current_timestamp) to compute "last 30 days".- If created_at is of type timestamp with time zone (timestamptz), comparisons with now() are timezone-aware and safe.- If created_at is timestamp without time zone, its values are interpreted relative to the database server's timezone; clarify with your DBA or convert to UTC. To be explicit when created_at stores UTC timestamps (common best practice), use:

sql

AND created_at >= (now() AT TIME ZONE 'UTC') - INTERVAL '30 days'

- Consider indexing (created_at, status, amount) for performance on large tables.

Practice Data Analyst questions across all topics

Additional Information

Want to create your own tailored preparation guide using our deep research?

Get Started for Free

Interview-Ready Courses

Visual-first, interactive, structured learning paths

Browse Data Analyst jobs

AI-enriched listings across hundreds of company career pages

Explore Jobs

Meta Data Analyst Interview Preparation Guide - Staff Level

Interview Process Overview

Interview Rounds

Recruiter Screening

What to Expect

Tips & Advice

Focus Topics

Meta Mission and Product Ecosystem Understanding

Practice Interview

Study Questions

Mentorship and Team Development Philosophy

Practice Interview

Study Questions

Career Narrative and Leadership Track Record

Practice Interview

Study Questions

Clear Communication and Influence

Practice Interview

Study Questions

Technical Phone Screen - SQL and Analytics

What to Expect

Tips & Advice

Focus Topics

Data Modeling and Schema Design Tradeoffs

Practice Interview

Study Questions

Problem Definition and Ambiguity Navigation

Practice Interview

Study Questions

Advanced SQL Patterns and Window Functions

Practice Interview

Study Questions

Meta Core Product Metrics

Practice Interview

Study Questions

Query Optimization and Performance

Practice Interview

Study Questions

On-site Round 1: Technical SQL Interview

What to Expect

Tips & Advice

Focus Topics

Query Efficiency and Optimization

Practice Interview

Study Questions

Data Quality and Validation Techniques

Practice Interview

Study Questions

SQL Code Quality and Best Practices

Practice Interview

Study Questions

Collaborative Technical Problem-Solving

Practice Interview

Study Questions

Complex Multi-Step Query Design

Practice Interview

Study Questions

On-site Round 2: Analytics and Product Sense

What to Expect

Tips & Advice

Focus Topics

Experimental Design and Testing

Practice Interview

Study Questions

Hypothesis-Driven Analytical Reasoning

Practice Interview

Study Questions

Metric Design and Hierarchies

Practice Interview

Study Questions

Core Meta Metrics and Frameworks

Practice Interview

Study Questions

Product Analytics and Strategy Thinking

Practice Interview

Study Questions

Problem Structuring and Ambiguity

Practice Interview

Study Questions

On-site Round 3: Behavioral - Leadership and Impact