General Technical Tool Proficiency Questions

Practical fluency with the technical productivity, analysis, and reporting tools used in your day-to-day work: spreadsheet and reporting software (for example Excel or Google Sheets), scripting or query languages (for example SQL, Python, R), data visualization or BI platforms (for example Tableau, Power BI, Looker), and domain-specific applications (for example CRM, ERP, contract or case management, marketing automation) where relevant to the role. Candidates should be able to describe their depth of expertise, typical use cases and real world examples, automation or scripting practices, and how they choose the right tool for a given problem. This topic also covers reproducible workflows, data preparation and cleaning, presenting findings clearly, and integrating tools into cross functional projects.

EasyTechnical

65 practiced

Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Include a short example (two small tables) showing how result row counts differ and a practical situation where each join type is appropriate in analytics work.

MediumTechnical

66 practiced

Given an events table:

events(user_id INT, occurred_at TIMESTAMP, region VARCHAR)

Write a SQL query that computes daily rolling 7-day active users per region (i.e., number of distinct users in the 7-day window ending on each day) using window functions or efficient SQL constructs. Explain how you'd optimize this for 100M+ rows.

Sample Answer

Approach: first reduce the raw events to one row per user per day per region (daily activity). Then for each region/day count distinct users in the 7-day window ending that day. Below is a clear, portable SQL (Postgres-compatible) using a pre-aggregation and a lateral correlated subquery; this is simple, correct, and easy to optimize for large tables.

SQL:

sql

WITH daily_user AS (
  SELECT
    region,
    user_id,
    (occurred_at::date) AS day
  FROM events
  GROUP BY region, user_id, (occurred_at::date)
),
days AS (
  SELECT DISTINCT region, day
  FROM daily_user
)
SELECT
  d.region,
  d.day,
  (
    SELECT COUNT(DISTINCT du.user_id)
    FROM daily_user du
    WHERE du.region = d.region
      AND du.day BETWEEN d.day - INTERVAL '6 day' AND d.day
  ) AS active_users_7d
FROM days d
ORDER BY d.region, d.day;

Why this works:- daily_user deduplicates within-day activity so counting distinct users later is accurate and much cheaper.- The correlated subquery counts distinct users across the 7-day window for each region/day.

Optimizations for 100M+ rows:- Pre-aggregate into a daily_user table (materialized table or nightly ETL) instead of scanning raw events on each run.- Indexes: (region, day, user_id) on daily_user to support the correlated subquery; consider composite index (region, day) plus user_id.- Partition daily_user by day (or month) to prune IO for date ranges.- If real-time not required, maintain a materialized view of region/day -> active_users_7d refreshed incrementally.- Use approximate distinct when exact counts are unnecessary: e.g., HLL (postgreSQL extension) or APPROX_COUNT_DISTINCT in BigQuery — much faster and low memory.- For analytics warehouses (BigQuery/Redshift/Snowflake), use built-in window COUNT(DISTINCT) or APPROX window functions or exploit ARRAY_AGG(DISTINCT) then cardinality if supported.- Parallelize: run per-region computations in parallel if regions are independent.

Edge notes:- Ensure timezone handling when casting occurred_at::date.- If events have deletes, ensure ETL handles late-arriving events and corrections.

MediumTechnical

66 practiced

In Tableau, you need to compute customer lifetime value (LTV) per acquisition cohort using Level of Detail (LOD) expressions. Given an orders table (user_id, order_date, amount, acquired_date), explain which LOD expressions you would use to compute per-user total revenue and then aggregate by acquisition cohort. Provide the formulas and a short explanation.

HardTechnical

81 practiced

A producer service will change the event schema (rename a field and add new properties). Describe a schema migration strategy to keep downstream analytics and dashboards working during the change: include versioning events, backward compatibility, consumer adaption, data contracts, testing strategy, and rolling cutover plan.

Sample Answer

Situation: The producer needs to rename a field and add properties without breaking downstream analytics and dashboards that rely on existing event shape.

Strategy (high-level):- Version events explicitly and preserve backward compatibility while consumers migrate.- Use a schema registry + strong data contract and a phased rollout with thorough testing and monitoring.

Concrete plan1. Versioning & compatibility- Introduce a new event version (e.g., event_v2) rather than mutating v1 in-place.- Use a schema format (Avro/JSON Schema/Protobuf) and enforce compatibility rules in the registry (allow both backward and forward compatible changes where possible).

2. Backward compatibility approaches- Producer temporarily emits both v1 and v2 (dual-write) or emits a single envelope with both field names: { "version":"v2", "payload":{ "newName":..., "oldName":... } }- If renaming, keep oldName populated from newName for a deprecation window.

3. Consumer adaptation (Data Analyst perspective)- Update ETL/SQL views and dashboard queries to read v2 fields, but continue to read v1 until migration completes.- Implement a consumer-side adapter (SQL view or Kafka Streams job) that normalizes events into a canonical analytics table: CREATE VIEW events_canonical AS SELECT COALESCE(payload.newName, payload.oldName) AS canonical_name, ... FROM events_raw;

4. Data contracts & governance- Document changes in a contract (expected fields, types, nullability, SLAs).- Announce change with timeline, owners, rollback contact, and deprecation date.- Require producer to provide sample events, schema diffs, and compatibility test results.

5. Testing strategy- Unit tests for producer schema generation.- Contract testing with a schema-registry-backed consumer test harness (e.g., Pact for events).- Integration tests: end-to-end pipeline run in staging, populate test dashboards.- Shadow/parallel run: route a copy of prod traffic to staging consumers to validate analytics outputs.

6. Rolling cutover plan- Phase 0: Communicate timeline + deprecation window (e.g., 4 weeks).- Phase 1: Producer emits v2 and continues to populate oldName for every event (dual fields).- Phase 2: Update ETL/views/dashboards to read canonical view or prefer v2; run in parallel and validate metrics (compare historical KPIs).- Phase 3: Switch dashboards to v2 canonical fields once parity confirmed (A/B or shadow comparisons within acceptable deltas).- Phase 4: After monitoring window and no regressions, producer stops filling oldName; finally deprecate v1 after grace period.

Monitoring & rollback- Create metric parity checks (counts, sums, key cohorts) comparing v1 vs v2 over time and alert on deviations.- Track downstream broken queries and dashboard errors; keep rollback plan to re-enable oldName population or revert producer to v1.

Outcome & learnings- This minimizes analytical interruptions, gives analysts time to update ETL and dashboards, enforces clear contracts, and provides measurable validation before final cutover.

EasyTechnical

64 practiced

Given a table events(user_id, event_date TIMESTAMP, revenue DECIMAL), write a SQL query using window functions to compute cumulative revenue per user ordered by event_date. Also show how you'd reset the cumulative total monthly (i.e., cumulative revenue per user per calendar month). Use ANSI SQL and show sample output columns.

Sample Answer

Approach: use SUM() as a window function partitioned by user and ordered by event_date to produce a running total. To reset monthly, include year+month in the partition keys so the window restarts each calendar month.

Overall cumulative per user:

sql

SELECT
  user_id,
  event_date,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue_to_date
FROM events
ORDER BY user_id, event_date;

Cumulative per user per calendar month (resets each month):

sql

SELECT
  user_id,
  event_date,
  revenue,
  EXTRACT(YEAR FROM event_date) AS year,
  EXTRACT(MONTH FROM event_date) AS month,
  SUM(revenue) OVER (
    PARTITION BY user_id, EXTRACT(YEAR FROM event_date), EXTRACT(MONTH FROM event_date)
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue_month_to_date
FROM events
ORDER BY user_id, year, month, event_date;

Sample output columns:- user_id- event_date- revenue- cumulative_revenue_to_date (overall running total)- year, month, cumulative_revenue_month_to_date (monthly reset running total)

Notes:- ORDER BY event_date should include tie-breaker (e.g., event_id) if multiple events share the same timestamp to make ordering deterministic.- EXTRACT is ANSI SQL; some systems offer DATE_TRUNC() if preferred.

Unlock Full Question Bank

Get access to hundreds of General Technical Tool Proficiency interview questions and detailed answers.

Join thousands of developers preparing for their dream job.