BI Analyst SQL Interview Prep: Metrics, Time Series, and Dashboards

BI analyst interviews are not pure SQL exams. SQL matters, but the interviewer is also looking for metric judgment: can you define the number, protect the grain, explain the dashboard, and investigate a change without guessing?

That is why generic SQL drills can feel incomplete. The BI version of a query usually asks a larger question: "What does this number mean, can we trust it, and what should happen next?"

This guide shows how to prepare for the SQL, dashboard, and metric-reasoning questions that commonly appear in BI analyst and data analyst interviews.

What BI Interviews Usually Test

Most BI analyst screens combine four skills:

• SQL fundamentals: joins, aggregation, filtering, date logic, window functions, and NULL handling.
• Metric judgment: defining active users, conversion, retention, revenue, churn, utilization, or another KPI in a way that matches the business question.
• Dashboard reasoning: explaining trends, spikes, drops, segments, and data quality checks.
• Communication: stating assumptions, asking clarifying questions, and turning query output into a recommendation.

Do not prepare as if the only goal is to produce a syntactically valid query. In BI roles, the query is the evidence; the business answer is the final deliverable.

The Core SQL Patterns to Practice

Start with patterns that appear in dashboards and recurring reporting work:

• One-to-many joins without inflated counts.
• COUNT(*) vs COUNT(column) vs COUNT(DISTINCT ...).
• Conditional aggregation with CASE WHEN.
• WHERE vs HAVING.
• Month-over-month or week-over-week comparisons.
• Top-N per group with ROW_NUMBER(), RANK(), or DENSE_RANK().
• Latest record per account, user, subscription, or ticket.
• Time ranges that include the start boundary and exclude the end boundary.

If joins are a weak spot, review why SQL joins create duplicate rows. If aggregation mistakes are the issue, review SQL GROUP BY errors and SQL COUNT with LEFT JOIN.

Practice Metric Definition Before Query Writing

A strong BI answer begins before the SQL. Define the metric and grain first.

Suppose the prompt is: "Calculate weekly active users." A weak answer starts typing immediately. A stronger answer says:

• "I will define active as at least one qualifying product event."
• "The output grain will be one row per week."
• "I will count distinct users, not event rows."
• "I will exclude internal users and test accounts if those flags exist."
• "I will use a half-open date range so week boundaries are not double-counted."

Now the SQL has a clear target:

select
    date_trunc('week', event_at) as week_start,
    count(distinct user_id) as weekly_active_users
from events
where event_at >= timestamp '2026-01-01'
  and event_at < timestamp '2026-04-01'
  and event_name in ('login', 'view_dashboard', 'run_report')
  and is_internal_user = false
group by date_trunc('week', event_at)
order by week_start;

The event names will differ by company. The habit is what matters: define the metric, define the grain, then query.

Time Series Questions

BI interviews use time series because dashboards are built around time. Practice questions like:

• Daily revenue by product line.
• Week-over-week change in active users.
• Rolling 7-day average of signups.
• Month-to-date revenue compared with the same day last month.
• Missing days in a dashboard trend.

For week-over-week comparisons, a window function is usually clearer than a self-join:

with weekly as (
    select
        date_trunc('week', event_at) as week_start,
        count(distinct user_id) as active_users
    from events
    where event_at >= timestamp '2026-01-01'
      and event_at < timestamp '2026-04-01'
    group by date_trunc('week', event_at)
)
select
    week_start,
    active_users,
    lag(active_users) over (order by week_start) as previous_week_active_users,
    active_users - lag(active_users) over (order by week_start) as week_over_week_change
from weekly
order by week_start;

If a chart needs every calendar day, use a date spine instead of hoping the event table contains every date. See SQL date spines and calendar tables.

Dashboard Spike or Drop Framework

A common BI prompt is: "A dashboard metric dropped yesterday. How would you investigate?"

Use a structured answer:

1. Confirm the metric definition. What exactly dropped: users, events, revenue, conversion rate, or a derived KPI?
2. Check data freshness. Did the pipeline finish? Are all source tables loaded through the expected timestamp?
3. Compare volume and rate. Did the numerator drop, the denominator increase, or both?
4. Segment the change. Break it down by platform, region, acquisition channel, product area, customer tier, or source system.
5. Check instrumentation. Did event names, tracking logic, filters, or dashboard joins change?
6. Estimate business impact. Quantify the size of the change and who is affected before proposing action.

This answer shows that you will not blame "the data" or "the dashboard" without evidence.

Questions Based on Query Output

Some interviews give you a query result and ask what it means. Practice reading output like an analyst:

• Does the grain match the question?
• Are counts inflated by duplicate rows?
• Are NULLs being treated as zero, unknown, or missing?
• Are incomplete time periods included?
• Could a filter in WHERE accidentally turn a LEFT JOIN into an inner join?
• Does the metric need distinct users, accounts, sessions, orders, or events?

When you explain output, do not jump straight to a recommendation. First say whether you trust the result and what checks you would run.

Sample BI Interview Question

Prompt: "Find each account's latest paid invoice and show whether the account paid more than its previous invoice."

One possible solution:

with paid_invoices as (
    select
        account_id,
        invoice_id,
        invoice_date,
        amount,
        row_number() over (
            partition by account_id
            order by invoice_date desc, invoice_id desc
        ) as invoice_rank,
        lag(amount) over (
            partition by account_id
            order by invoice_date, invoice_id
        ) as previous_paid_amount
    from invoices
    where status = 'paid'
)
select
    account_id,
    invoice_id,
    invoice_date,
    amount,
    previous_paid_amount,
    case
        when previous_paid_amount is null then 'no previous paid invoice'
        when amount > previous_paid_amount then 'increased'
        when amount < previous_paid_amount then 'decreased'
        else 'unchanged'
    end as invoice_change
from paid_invoices
where invoice_rank = 1;

After writing the query, explain the assumptions: paid invoices only, one output row per account, a deterministic latest-invoice tie-breaker, and a separate label for accounts with no previous paid invoice.

Conceptual Questions to Prepare

Practice concise answers to these:

• How do you choose the right KPI for a dashboard?
• How do you decide whether a metric changed because of behavior or tracking?
• How do you validate a dashboard before sharing it?
• What is the difference between a fact table and a dimension table?
• What makes a dashboard actionable instead of decorative?
• How would you explain a SQL result to a non-technical stakeholder?

The best answers combine business context with data checks. For data modeling concepts, review SQL data modeling for analyst interviews.

How to Structure Your Answer Live

Use this sequence when you get a BI SQL prompt:

1. Restate the question in business terms.
2. Define the output grain.
3. List assumptions and exclusions.
4. Write the query in small, named steps.
5. Validate row counts, date ranges, and duplicates.
6. Translate the result into a business interpretation.

This structure helps even when you do not finish every line of SQL. Interviewers can see how you reason, and they can give hints at the right level.

A Practical Prep Plan

For one week of focused BI interview prep:

• Day 1: Joins, grain, duplicate rows, and count traps.
• Day 2: Aggregation, CASE WHEN, and WHERE vs HAVING.
• Day 3: Date bucketing, rolling averages, and incomplete periods.
• Day 4: Window functions for latest row, ranking, and previous-period comparison.
• Day 5: Metric definition and dashboard spike/drop investigations.
• Day 6: Two timed SQL prompts, followed by spoken result explanations.
• Day 7: Review mistakes and prepare stories about real dashboards, messy data, and stakeholder tradeoffs.

The Main Takeaway

BI analyst interviews reward candidates who connect SQL to business judgment. Practice enough syntax to be fluent, but spend equal time defining metrics, checking data quality, reading dashboard output, and explaining what the result means.

Candidates stand out when they can state the grain, protect the metric, investigate anomalies, and communicate the answer clearly. That is stronger than memorizing isolated SQL patterns.