SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
ORDER BY total_spend DESC
LIMIT 5;

Explanation: Use GROUP BY to aggregate spend by customer, ORDER BY DESC to sort highest first, and LIMIT 5 to get top customers.

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
);

Explanation: Use NOT IN with a subquery that finds customers with recent orders. This returns customers with no orders in the last 90 days.

INNER JOIN returns only rows where there's a match in both tables. LEFT JOIN returns all rows from the left table, and matching rows from the right table. If no match, NULLs are returned for right table columns.

SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS monthly_sales,
    SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Explanation: Use a window function with SUM() OVER() to calculate the cumulative total. ORDER BY in the window function determines the running order.

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row. Use them for running totals, moving averages, ranking, and percentiles.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Use IS NULL and IS NOT NULL to check for NULLs. Use COALESCE() to replace NULL with a default value. Use COUNT(*) vs COUNT(column) — COUNT(*) counts all rows, COUNT(column) counts non-NULL values.

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

WHERE filters rows before aggregation. HAVING filters after aggregation. Use WHERE for individual row conditions, HAVING for conditions on aggregate functions like SUM(), COUNT().

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month,
           SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
       total_sales,
       LAG(total_sales, 1) OVER (ORDER BY month) AS prev_month_sales,
       ROUND(((total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) / 
              LAG(total_sales, 1) OVER (ORDER BY month)) * 100, 2) AS growth_pct
FROM monthly_sales
ORDER BY month;

A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Use CTEs to break complex queries into simpler parts, improve readability, and avoid subqueries.

SELECT customer_id, 
       DATE_TRUNC('month', order_date) AS month,
       COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
HAVING COUNT(order_id) > 5
ORDER BY customer_id, month;

UNION combines results from multiple SELECT statements and removes duplicates. UNION ALL combines results without removing duplicates. UNION ALL is faster because it doesn't check for duplicates.

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
ORDER BY department_id, rank;

Common optimization techniques: Use indexes on columns used in WHERE and JOIN clauses, avoid SELECT * (select only needed columns), use EXPLAIN to understand query execution, avoid functions in WHERE clauses on indexed columns, and optimize JOIN order.

import pandas as pd
df = pd.read_csv('file.csv')
print(df.head())

Options: Use df.dropna() to remove rows with missing values, df.fillna(value) to fill with a value, or df.fillna(method='ffill') to forward fill. You can also impute with mean, median, or mode using df.fillna(df.mean()).

import pandas as pd

def clean_dates(df, column):
    df[column] = pd.to_datetime(df[column], errors='coerce')
    return df

NumPy is for numerical computing with arrays. Pandas is built on NumPy and provides data structures like DataFrames and Series, making data manipulation and analysis easier with labeled columns and rows.

df.groupby('category')['sales'].agg(['sum', 'mean', 'count', 'max', 'min'])

# Inner join
df_merged = pd.merge(df1, df2, on='key_column')

# Left join
df_merged = pd.merge(df1, df2, on='key_column', how='left')

# Different column names
df_merged = pd.merge(df1, df2, left_on='key1', right_on='key2')

apply() is used to apply a function to rows or columns of a DataFrame. map() is used on Series to apply a function element-wise. applymap() applies a function element-wise to the entire DataFrame.

pivot = df.pivot_table(
    values='sales',
    index='region',
    columns='product',
    aggfunc='sum',
    fill_value=0
)

Common methods: Use Z-score (scipy.stats.zscore) to identify outliers beyond a threshold (e.g., 3 standard deviations). Use IQR (Interquartile Range) method — define outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR.

import matplotlib.pyplot as plt

def create_bar_chart(x, y, title, xlabel, ylabel):
    plt.figure(figsize=(10, 6))
    plt.bar(x, y)
    plt.title(title)
    plt.xlabel(xlabel)
    plt.ylabel(ylabel)
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.show()

loc is label-based indexing (uses row/column labels). iloc is integer position-based indexing (uses row/column indices). Example: df.loc['row_label'] vs df.iloc[0].

df.to_csv('output.csv', index=False)

df['profit'] = df['revenue'] - df['cost']
df['category'] = df['value'].apply(lambda x: 'High' if x > 100 else 'Low')

Seaborn is a Python data visualization library built on matplotlib. It provides a high-level interface for drawing statistical graphics, including more attractive default styles and color palettes.

Use pd.get_dummies() for one-hot encoding. Use LabelEncoder from scikit-learn for label encoding. For ordered categories, use pd.Categorical() with ordered=True.

Mean is the average of all values. Median is the middle value when sorted. Mode is the most frequent value. Median is robust to outliers, mean is sensitive to outliers.

Standard deviation measures the spread of data around the mean. It's important because it tells you how much variation exists in your data. A small standard deviation means data points are close to the mean, a large one means they're spread out.

Normal distribution is a bell-shaped curve where data is symmetric around the mean. Properties: 68% of data within 1 standard deviation, 95% within 2 standard deviations, 99.7% within 3 standard deviations. Mean = median = mode.

The Central Limit Theorem states that the sampling distribution of the sample mean approaches a normal distribution as the sample size increases, regardless of the population distribution. This is why many statistical tests assume normality.

A p-value is the probability of observing results as extreme as those observed, assuming the null hypothesis is true. A low p-value (typically < 0.05) indicates strong evidence against the null hypothesis, suggesting a statistically significant result.

Type I error (false positive) — rejecting the null hypothesis when it's actually true. Type II error (false negative) — failing to reject the null hypothesis when it's actually false. Alpha is the probability of Type I error, Beta is the probability of Type II error.

Correlation measures the strength and direction of a linear relationship between two variables. It doesn't imply causation because confounding variables, reverse causation, or spurious relationships can exist. Always consider the context and use experiments for causal inference.

Descriptive statistics summarize and describe data (mean, median, mode, standard deviation). Inferential statistics make predictions or inferences about a population based on sample data (hypothesis testing, confidence intervals).

A confidence interval is a range of values that likely contains the true population parameter with a certain level of confidence (usually 95%). It provides both a point estimate and a measure of uncertainty.

Population is the entire group you're interested in. Sample is a subset of the population used to represent it. Statistical inference uses sample data to draw conclusions about the population.

Sample answer (STAR method): "In my previous role, the sales team was spending 3 days manually compiling weekly reports. I built a Tableau dashboard connected to the SQL database that automated reporting. Reporting time dropped from 3 days to 2 hours, and 12 sales reps started using it daily."

Sample answer: "I once worked with a marketing manager who wanted complex analysis done daily. I scheduled a meeting to understand their actual needs, identified the key metrics they cared about, and built a dashboard that auto-refreshed daily. They appreciated the transparency and the solution."

Sample answer: "I prioritize tasks, communicate with stakeholders about what's realistic, and focus on delivering the most critical analysis first. I also automate repetitive tasks to save time. I've never missed a deadline."

Sample answer: "I start with hands-on projects. I read documentation, watch tutorials, and immediately apply what I learn. I recently taught myself Power BI by building a dashboard for a side project. Within 2 weeks, I was confident enough to use it in production."

Sample answer: "Early in my career, I wrote a SQL query that returned incorrect data because I forgot a JOIN condition. I discovered it before it was used in reports, fixed it immediately, and added a checklist for future queries. I also documented the mistake to prevent recurrence."

Sample answer: "I avoid technical jargon. I use simple language and analogies. I focus on what the findings mean for the business, not how I got them. I also use visualizations — a chart is often easier to understand than a table of numbers."

Sample answer: "A stakeholder asked for a simple report on sales trends. I analyzed the data further and found a correlation with marketing spend. I presented both the trend and the recommendation to increase spend on underperforming channels. The team adopted the recommendation and sales increased by 8%."

Sample answer: "I prioritize based on impact and urgency. I use a simple framework: high impact + high urgency = do first. High impact + low urgency = schedule. Low impact = do last. I also communicate with stakeholders to align priorities."

Sample answer: "I love solving problems and finding patterns. Data analytics allows me to combine my analytical thinking with real business impact. I enjoy translating data into actionable insights that drive decisions."

Sample answer: "I see myself as a senior data analyst or moving into a data science role. I want to continue building my technical skills while also developing my leadership and communication abilities to mentor others."

Practice SQL daily, review Python pandas basics, brush up on statistics, and prepare STAR answers for behavioral questions. Mock interviews are also very helpful.

Most data analyst interviews have 3-5 SQL questions, ranging from basic SELECT/JOIN to complex window functions and subqueries.

Be honest and say "I don't know, but here's how I would approach it." Show your problem-solving process — interviewers value this more than a perfect answer.

Very important. Behavioral questions assess your soft skills, problem-solving, and cultural fit. They're often the deciding factor between two equally technical candidates.

Yes — bring your laptop and be ready to show 1-2 projects. Walk through your process, challenges, and results. This is often more impressive than a perfect answer to a question.