Company-wise interview questions

Same 6-step structure: problem → data → tools → process → insights → impact. Tailor the domain if you know the company's clients.

Total Sales, Profit %, YoY growth with SAMEPERIODLASTYEAR, rolling 3-month average with DATESINPERIOD, and Top-N ranking with RANKX — pick 4–5 you can write live.

VALUES returns a table of distinct visible values; SELECTEDVALUE returns the single selected value or an alternate if multiple/none — ideal for dynamic titles like "Sales for " & SELECTEDVALUE(Region[Name], "All Regions").

Star schema, removed unused columns, measures instead of calculated columns, single-direction relationships, limited visuals per page, page-level filters, incremental refresh on the fact table, and verified with Performance Analyzer — quote before/after load time if you can.

Created roles in Modeling → Manage Roles with a DAX rule, e.g. [Region] = USERPRINCIPALNAME() mapped via a user-region table; tested with "View As Role"; assigned members to roles in the Service. Mention dynamic RLS (one rule, user table) vs static roles.

TREATAS(values, column) applies a set of values as a filter on a column as if a relationship existed — the standard way to filter across tables with no physical relationship. Example: CALCULATE([Sales], TREATAS(VALUES(Budget[Region]), Sales[Region])).

The direct boolean filter inside CALCULATE is faster — it filters the column via the storage engine, while FILTER() iterates row by row in the formula engine. Use FILTER only when the condition genuinely needs row context (comparing columns to each other).

Have three ready with definitions, e.g.: Total Revenue (SUM of sales), Profit Margin % (profit/revenue), YoY Growth % — and one sentence on why each mattered to the business. Domain-fit KPIs (churn %, approval rate) are even better.

Walk through one measure end-to-end, e.g. YoY%: base measure → CALCULATE with SAMEPERIODLASTYEAR → DIVIDE for safe division → why DIVIDE over "/" (handles divide-by-zero). Depth on one beats listing ten.

Example: "Regional managers should see only their region — I built dynamic RLS with a UserAccess table (email ↔ region), rule [Region] IN CALCULATETABLE(VALUES(UserAccess[Region]), UserAccess[Email] = USERPRINCIPALNAME()), tested via View As Role."

SELECT LENGTH('a,b,c,d,e')
     - LENGTH(REPLACE('a,b,c,d,e', ',', '')) AS comma_count;
-- SQL Server: use LEN() instead of LENGTH()  → returns 4

Classic trick: total length minus length without commas = number of commas.

SELECT * FROM (
  SELECT month, customer, revenue,
         DENSE_RANK() OVER (PARTITION BY month
                            ORDER BY revenue DESC) AS rnk
  FROM sales
) t
WHERE rnk <= 3;

PARTITION BY month restarts the ranking every month — top 3 per month.

Example answer: "ROW_NUMBER to de-duplicate records keeping the latest per customer, and LAG to compute month-over-month change — both avoided messy self-joins and kept queries readable."

Structure: business problem → sources → how you modeled/joined data → dashboards built (name the charts) → interactivity (filters, actions, parameters) → performance work → impact. Emphasize the developer parts: calculations, LODs, publishing, refresh schedules.

Drag the dimension to Color and the measure to Angle on the Marks card (set mark type to Pie) — or select dimension + measure and hit the pie in Show Me. Add labels (dimension + measure, quick table calc → percent of total).

Create a calculated field: Last Name = TRIM(SPLIT([Customer Name], " ", -1)) — SPLIT with index −1 takes the last token. Drag it next to Customer Name on Rows.

Calculated field: DATEADD('day', -1, DATETRUNC('month', TODAY())) — truncate today to the 1st of this month, minus one day = last day of previous month. Use it in a filter or as a reference.

Moved live connections to extracts (aggregated, filtered), reduced quick filters and replaced "Only relevant values" where costly, cut marks per view, replaced heavy table calcs with LODs or source-side logic, and verified with Performance Recording — mention a before/after if possible.

Entitlement-table approach: joined a UserAccess table, data source filter USERNAME() = [User Email]; published with the filter locked so every viewer sees only their rows. Contrast with manual user filters and say why the calculated approach scales.

Slower visuals (every interaction = live query), limited Power Query transformations, several DAX functions restricted or slow (time intelligence needs a date table), 1-million-row limit per query result, no calculated tables on DQ sources, and total dependence on source database performance.

Running Total =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Or simply TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]) for a year-bounded running total.

Dealer Revenue =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Channel] = "Dealer"
)

Pattern: base aggregation + a filter on the channel/segment column inside CALCULATE.

Cumulative Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

ALL removes the date filter, then we re-filter to "all dates up to the current one" — the classic cumulative pattern.

% of Total =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Numerator respects current filters; denominator ignores them via ALL — format as percentage. Use ALLSELECTED instead of ALL to respect slicer selections.

Every row of x matches every row of z (all values are 1), so joins produce a cartesian match:

• INNER JOIN: 5 × 3 = 15 rows
• LEFT JOIN: every x row has matches → 15 rows
• RIGHT JOIN: every z row has matches → 15 rows
• FULL JOIN: no unmatched rows on either side → 15 rows

This is one of the most famous trick questions — remember: duplicate join keys multiply.

UNION combines results and removes duplicates (implicit sort/dedup cost); UNION ALL keeps everything, so it's faster — use UNION ALL whenever duplicates are impossible or acceptable.

DELETE removes rows one by one, supports WHERE, is fully logged and can be rolled back; TRUNCATE instantly removes all rows, keeps the structure, resets identity, minimal logging, no WHERE. TRUNCATE is DDL-like and much faster for emptying a table.

Filter context is the set of filters active on a calculation at evaluation time — coming from slicers, rows/columns of visuals, page filters and CALCULATE. The same measure returns different values in each cell because each cell has a different filter context. CALCULATE is the function that modifies filter context.

Modeling → Manage Roles → create a role with a DAX rule (e.g. [Region] = "West" for static, or [Email] = USERPRINCIPALNAME() against a user table for dynamic RLS) → test with View As Role → publish → in the Service, assign users/groups to the role under dataset Security.

Visual-level (one visual), page-level (all visuals on a page), report-level (whole report), drill-through filters (carried to a detail page), slicers (user-facing), and cross-filtering between visuals. Plus filters inside DAX via CALCULATE.

ALL(table/column) removes every filter — grand total regardless of slicers. ALLSELECTED() removes only the filters inside the visual but respects outside selections (slicers/page filters). % of total with ALL = share of everything; with ALLSELECTED = share of what the user selected.

Laptop Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Products[ProductName] = "Laptop"
)

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

LEFT JOIN keeps the CEO (manager_id NULL) in the result with a NULL manager.

SELECT * FROM (
  SELECT name, department, join_date,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY join_date DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

ROW_NUMBER partitioned by department, ordered by join_date descending — rn = 1 is the newest joinee per department.

d = {"banana": 2, "apple": 5}
d["cherry"] = 7          # add
d["apple"] = 10          # modify
for k in sorted(d):      # alphabetical keys
    print(k, d[k])

from collections import Counter
nums = [1, 2, 2, 3, 3, 3, 4]
counts = Counter(nums)
for value, cnt in counts.items():
    print(value, "appears", cnt, "times")

from collections import Counter
nums = [1, 2, 2, 3, 3, 3, 4]
for value, cnt in Counter(nums).items():
    if cnt > 1:
        print(value, "is duplicated", cnt, "times")

1. Requirements: meet stakeholders — which KPIs, which grain, who will use it.
2. Data: connect sources (SQL/Excel), profile the data.
3. Clean: Power Query — types, duplicates, nulls, unpivot targets.
4. Model: star schema — Sales fact + Date/Product/Customer/Region dims, mark the date table.
5. DAX: Total Sales, Profit %, YoY, YTD measures.
6. Design: KPI cards on top, trends middle, detail matrix below; slicers; drill-through; consistent theme.
7. Validate: tie numbers to source reports.
8. Deploy: publish, gateway + scheduled refresh, RLS, share via app; collect feedback and iterate.

Layered answer: data model (star schema, drop unused/high-cardinality columns, integer keys, single-direction relationships) → DAX (measures over calculated columns, avoid row-by-row FILTER when a boolean filter works) → source (query folding, incremental refresh, aggregations) → report (fewer visuals, page filters, reduce interactions) → diagnose with Performance Analyzer and fix the top offenders first.

• Bar/Column: compare values across categories (sales by region).
• Line: trends over time (monthly revenue).
• Pie/Donut: composition — parts of a whole, few categories.
• Scatter: relationship between two measures, spotting outliers (discount vs profit).
• Matrix/Heatmap: values across two dimensions with conditional color (region × month sales intensity).

For salaries 100, 100, 90: ROW_NUMBER → 1,2,3 · RANK → 1,1,3 (gap) · DENSE_RANK → 1,1,2 (no gap).

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employee;

SELECT salary FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) t
WHERE rnk = @n;   -- e.g. 3 for 3rd highest

WITH team AS (
  SELECT EmpID, ManagerID
  FROM employee
  WHERE ManagerID = @manager_id      -- direct reports
  UNION ALL
  SELECT e.EmpID, e.ManagerID
  FROM employee e
  JOIN team t ON e.ManagerID = t.EmpID  -- their reports, recursively
)
SELECT * FROM team;

A recursive CTE — the anchor gets direct reports; the recursive part walks down the tree until no more levels.

SELECT Dept, EmpID, JoinDate, Salary,
       SUM(Salary) OVER (PARTITION BY Dept
                         ORDER BY JoinDate
                         ROWS UNBOUNDED PRECEDING) AS cumulative_salary
FROM employee
WHERE JoinDate >= DATEADD(DAY, -30, GETDATE());

SELECT * FROM (
  SELECT CustomerID, ProductCategory, OrderAmount,
         DENSE_RANK() OVER (PARTITION BY ProductCategory
                            ORDER BY OrderAmount DESC) AS rnk
  FROM customer
) t
WHERE rnk <= 2;

DENSE_RANK keeps tied customers together — if two tie at rank 1, both appear (that's "handling ties appropriately").

Formula: genuine origin story (what pulled you to data) + proof of commitment (projects/upskilling) + 1–2 researched, specific reasons for the company (their domain, clients, culture). Avoid generic lines like "big brand name".

Answer in STAR: Situation (context) → Task (what was needed by when) → Action (prioritized, broke work down, communicated early, automated a step) → Result (delivered on time + a number). Pick a real story; interviewers probe details.

Running Total Monthly =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALLSELECTED('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Or year-bounded: TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]).

A bookmark captures the state of a page — filters, slicers, visual visibility, sort. Combined with buttons and the Selection pane, bookmarks build toggle views (chart ↔ table), pop-up filter panels, and guided story navigation. Create via View → Bookmarks → Add.

SUM(column) aggregates one existing column. SUMX(table, expression) is an iterator — evaluates the expression row by row then sums, needed when the value must be computed per row first, e.g. SUMX(Sales, Sales[Qty] * Sales[Price]). SUM is faster; use SUMX only when row-level math is required.

ALL ignores every filter (true grand total); ALLSELECTED ignores filters inside the visual but respects user selections (slicers). % of grand total → ALL; % of the user's current selection → ALLSELECTED.

Clustered: defines the physical order of table rows — one per table (usually the primary key); the table is the index. Non-clustered: a separate structure with pointers to rows — many allowed per table; great for frequent WHERE/JOIN columns. Analogy: clustered = dictionary order, non-clustered = index at the back of a book.

CTE: temporary named result inside one query — vanishes after execution, supports recursion, improves readability. View: a saved query object in the database — reusable across queries/users, can have permissions, can be indexed (materialized). Rule: one-off logic → CTE; reusable logic → view.

ROW_NUMBER never repeats; RANK repeats and skips; DENSE_RANK repeats without gaps.

SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
-- or
SELECT DISTINCT salary FROM employee
ORDER BY salary DESC LIMIT 1 OFFSET 1;

Star: denormalized dimensions directly around the fact table — fewer joins, faster, BI-friendly. Snowflake: dimensions normalized into sub-tables — saves storage, more joins, used for very large dimensions.

Describe your genuinely hardest query: multi-CTE pipeline, window functions for ranking/running totals, self-join for hierarchy, conditional aggregation with CASE — walk through one real example and why it was complex.

Honest analyst answer: aggregate functions (SUM/COUNT/AVG) with GROUP BY, JOINs daily, window functions (ROW_NUMBER, DENSE_RANK, LAG), CASE WHEN, date functions (DATEADD/DATEDIFF), string functions (CONCAT, SUBSTRING, TRIM) and COALESCE for nulls.

State your project domain (retail, banking, healthcare, manufacturing…) and add one domain-specific metric you handled (e.g. inventory turnover for retail) — that one detail makes it credible.

Apply normal forms step by step: 1NF — atomic values, no repeating groups; 2NF — remove partial dependencies on a composite key; 3NF — remove transitive dependencies (non-key depending on non-key). Result: each fact stored once, linked via keys.

Normalized: many small related tables, no redundancy — best for OLTP writes and integrity. Denormalized: merged wider tables with some duplication — fewer joins, faster reads, standard for analytics/warehouses (star schema is deliberate denormalization).

Ideal answer: "Working knowledge — Power Automate for flows like refresh-completion alerts or emailing reports, Power Apps for simple data-entry apps that write back to a source Power BI reads. I integrate them with Power BI within the Power Platform." Be honest about depth.

Conformed (shared across facts, e.g. Date), Slowly Changing (attributes change over time — SCD types), Junk (mixed low-cardinality flags), Degenerate (e.g. invoice number stored in the fact), Role-playing (one Date dim used as Order/Ship/Delivery date).

Data mart: a subject-focused slice of the warehouse (Sales mart) for one department. In Power BI, Dataflow = reusable cloud Power Query ETL (entities stored in the service), while Power BI's Datamart feature = dataflow + managed SQL database + dataset in one self-service package.

Yes — dynamic RLS uses USERPRINCIPALNAME() (or USERNAME()) in the role rule, matched against a user-access table: [Email] = USERPRINCIPALNAME(). One rule serves every user via the mapping table.

Slowly Changing Dimensions — how dimension attribute changes are stored: Type 0 never change; Type 1 overwrite (no history); Type 2 add a new row with start/end dates + current flag (full history — most used); Type 3 previous-value column (limited history); higher types combine these.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

SELECT SUM(amount) AS last_year_revenue
FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE()) - 1;
-- or with LAG for a year-over-year table:
SELECT yr, revenue,
       LAG(revenue) OVER (ORDER BY yr) AS prev_year_revenue
FROM yearly_sales;

SQL: aggregate per period and use LAG() to bring the previous period onto the same row, then compute the difference/%. Power BI: time-intelligence DAX — SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD against a proper date table.

They read another row's value without a self-join: LAG(x) = previous row, LEAD(x) = next row (per ORDER BY, optionally per PARTITION). Classic uses: month-over-month change, days between consecutive orders, comparing a row with the next event.

SELECT TOP 10 * FROM sales ORDER BY revenue DESC;     -- SQL Server
SELECT * FROM sales ORDER BY revenue DESC LIMIT 10;   -- MySQL/Postgres

For top 10 per group, use ROW_NUMBER() OVER (PARTITION BY group ORDER BY revenue DESC) and filter ≤ 10.

SELECT
  SUM(CASE WHEN CAST(order_date AS DATE) = CAST(GETDATE() AS DATE)
       THEN amount END) AS today_sales,
  SUM(CASE WHEN CAST(order_date AS DATE) =
       CAST(DATEADD(YEAR,-1,GETDATE()) AS DATE)
       THEN amount END) AS same_day_last_year
FROM orders;

Yes — GROUP BY ROLLUP(region, category) produces subtotals per region and a grand total in one query; CUBE gives all combinations; GROUPING() identifies subtotal rows.

SELECT SUM(x) AS total_x,
       SUM(x - y) AS total_difference   -- = SUM(x) - SUM(y)
FROM t;

ALL removes filters from an entire table/column. ALLEXCEPT(table, col1…) removes all filters except the listed columns — e.g. total per region ignoring everything else: CALCULATE(SUM(Sales[Amt]), ALLEXCEPT(Sales, Sales[Region])).

Joins: INNER (matches only), LEFT/RIGHT (keep one side), FULL (both), CROSS (cartesian), SELF (table with itself). Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT — rules that protect data integrity.

A table that lives only for the session (#temp in SQL Server) or transaction — used to store intermediate results reused across multiple statements, e.g. staging a filtered dataset before several analyses.

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY email ORDER BY id) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

CTE — temporary, exists only inside one query, supports recursion. View — a stored, reusable query object with permissions; indexed views can even persist results. One-off readability → CTE; shared reusable logic → View.

1. Get the execution plan; find scans, spills and heavy operators.
2. Add/repair indexes on join/filter columns; update statistics.
3. Replace cursors/row-by-row logic with set-based operations.
4. Break giant queries into indexed temp-table steps; filter early; remove SELECT *.
5. Check parameter sniffing (OPTION(RECOMPILE) / local variables), blocking and tempdb pressure.

A model that combines Import and DirectQuery sources: small dimensions imported for speed, huge fact tables on DirectQuery for freshness, with dual-storage tables bridging both. Best-of-both approach for big data.

Use a composite model: the dynamic table on DirectQuery (always current) and the static one on Import. Alternative: both Import but exclude the static table from refresh ("Include in report refresh" off in Power Query).

DirectQuery sends live SQL to the source per interaction — always fresh, slower. Direct Lake (Fabric) reads Delta/Parquet files in OneLake directly into the VertiPaq engine — near-import speed without copying/refreshing data. Direct Lake ≈ import performance + DirectQuery freshness.

Import is better for most reports: fastest visuals, full DAX/Power Query. DirectQuery when data is too large to import, must be real-time, or must stay in the source for compliance. Composite when you need both.

Power BI's in-memory columnar storage engine behind Import mode: stores data column-wise, heavily compressed (dictionary + run-length encoding), scans only needed columns — that's why imported models with fewer, low-cardinality columns fly.

A relationship setting: Single — filters flow one way (dimension → fact; the recommended default) or Both — filters flow both ways (needed for some many-to-many cases, but risks ambiguity and slowness — use sparingly).

Star schema: Region (1) → Customer (many) as a snowflaked arm, or better flatten region attributes into the Customer dimension → Customer (1) → Sales (many). Filters then flow Region → Customer → Sales naturally.

Distinct Products Sold = DISTINCTCOUNT(Sales[ProductID])

In SQL: COUNT(DISTINCT product_id). In DAX, DISTINCTCOUNT on the fact table's product key counts only products that actually appear in sales.

Options: create the relationship on a shared key if one exists; use a bridge table for many-to-many; or filter virtually in DAX with TREATAS(VALUES(Products[ID]), Sales[ProductID]) when a physical relationship isn't possible.