BI and Reporting Developer Interview Questions &amp; Answers

Show answer

A composite key is a primary key made of two or more columns that together uniquely identify a row. For example, in an enrolments table, student_id and course_id together form the key because a student can join many courses and a course has many students, but each pair appears once. Column order in a composite key matters for index usage.

What is a primary key and a foreign key?

Show answer

A primary key uniquely identifies each row in a table and cannot be NULL or duplicate, such as student_id. A foreign key is a column in one table that references the primary key of another, such as orders.customer_id referencing customers.customer_id. Foreign keys enforce referential integrity, preventing orders for customers that do not exist.

Find duplicate email addresses in a users table.

Show answer

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; To see full duplicate rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email; Normalise case with LOWER(email) if needed.

Write a query to list orders with customer names.

JoinsEasy

Show answer

SELECT o.order_id, o.order_date, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC; Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.

What are equi joins, non-equi joins and natural joins?

JoinsMedium

Show answer

An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.

Produce region subtotals and a grand total in one query.

Show answer

SELECT COALESCE(region, 'All Regions') AS region, COALESCE(city, 'Subtotal') AS city, SUM(sales) AS sales FROM store_sales GROUP BY ROLLUP(region, city) ORDER BY region, city; In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.

What is a CROSS JOIN and when is it useful?

JoinsEasy

Show answer

A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.

What is a composite index and why does column order matter?

Indexes and PerformanceEasy

Show answer

A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.

Design tables for students, courses and enrolments.

Show answer

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE enrolments ( student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), enrolled_on DATE, PRIMARY KEY (student_id, course_id) ); The junction table resolves the many-to-many relationship.

What is a self join? Give an example.

JoinsEasy

Show answer

A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.

What happens when you join on a column that has NULL values?

JoinsEasy

Show answer

NULL never equals NULL in a join condition, so rows with NULL in the join column do not match each other in an inner join. In a LEFT JOIN, left rows with NULL keys are kept but get NULLs for right columns. If you really need NULLs to match, use a condition such as a.key IS NOT DISTINCT FROM b.key, or COALESCE both sides carefully.

You need customers who bought both product A and product B. How would you write it?

JoinsMedium

Show answer

Group by customer and count distinct matching products: SELECT customer_id FROM order_items WHERE product_id IN ('A', 'B') GROUP BY customer_id HAVING COUNT(DISTINCT product_id) = 2. Alternatively use INTERSECT of two queries, or two EXISTS conditions. The HAVING approach is concise, scans the table once and is easy to extend to three or more products.

Tell me about a time you automated a manual data task with SQL.

SQL BasicsMedium

Show answer

Situation: the operations team spent four hours every Monday merging Excel files for a weekly report. Task: reduce the effort. Action: I loaded the files into staging tables, wrote SQL with joins and aggregations to produce the report, and scheduled it as a job. Result: the report was ready automatically at 8 AM, saving about 16 hours a month and removing copy-paste errors.

What are aggregate functions in SQL?

Show answer

Aggregate functions compute a single value from many rows: COUNT, SUM, AVG, MIN and MAX are standard, and databases add others such as STRING_AGG, GROUP_CONCAT, STDDEV and MEDIAN or PERCENTILE_CONT. Used with GROUP BY they return one value per group; without it they return one value for the whole table. Except COUNT(*), they ignore NULL values.

How does AVG treat NULL values?

Show answer

AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Show answer

The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.

Find customers who have never placed an order.

JoinsEasy

Show answer

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; Alternative: SELECT customer_id, name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );

What is the difference between clustered and non-clustered indexes?

Indexes and PerformanceEasy

Show answer

A clustered index defines the physical order of rows in the table, so there can be only one, and in SQL Server the primary key creates it by default. A non-clustered index is a separate structure with key values and pointers to rows, and a table can have many. Range queries on the clustered key are very fast, while non-clustered indexes suit selective lookups.

What is a transaction in SQL?

Transactions and Advanced SQLEasy

Show answer

A transaction is a group of SQL statements executed as a single unit of work that either fully succeeds or fully fails. It starts with BEGIN or START TRANSACTION and ends with COMMIT to save changes or ROLLBACK to undo them. For a fund transfer, debiting one account and crediting another must happen together, so both statements run in one transaction.

Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?

Show answer

Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.

Write a query to show each employee with their manager's name.

JoinsMedium

Show answer

SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.

How do you keep improving your SQL skills?

JoinsEasy

Show answer

Situation: after college I knew only basic SELECT queries. Task: become interview-ready for data roles. Action: I solved two practice problems daily, read execution plans for my own queries, revised window functions weekly and built a small project analysing public sales data in PostgreSQL. Result: within three months I could solve medium-level problems confidently, and I continue practising regularly.

Find the number of employees and average salary in each department.

Show answer

SELECT department_id, COUNT(*) AS emp_count, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department_id ORDER BY emp_count DESC; Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

Show answer

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.

What is the difference between a join and a union?

JoinsMedium

Show answer

A join combines columns from different tables side by side based on a matching condition, making rows wider. A union stacks results of queries with the same column structure vertically, making the result longer. Use a join to bring customer names next to orders, and a union to combine 2024 and 2025 order tables into one list.

A LEFT JOIN is returning fewer rows than the left table. Why could that happen?

JoinsMedium

Show answer

A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.

Can you use a column alias in GROUP BY or HAVING?

Show answer

In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.

What is GROUP BY and what is the rule for selected columns?

Show answer

GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.

A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?

Show answer

Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.

How do you read an EXPLAIN ANALYZE output in PostgreSQL?

Indexes and PerformanceMedium

Show answer

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101; Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.

After joining orders with payments, revenue doubled. What went wrong?

JoinsMedium

Show answer

Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.

Explain 1NF, 2NF and 3NF with simple examples.

Show answer

1NF: each column holds atomic values and there are no repeating groups, so no comma-separated phone numbers. 2NF: in 1NF and every non-key column depends on the whole composite key, so product name should not sit in an order_items table keyed by order and product. 3NF: in 2NF with no transitive dependency, so city should not determine state inside the employees table.

What are constraints in SQL?

Show answer

Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.

What is denormalisation and when is it used?

Show answer

Denormalisation intentionally adds redundancy, such as storing customer name in an orders reporting table, to reduce joins and speed up reads. It is common in data warehouses, reporting tables and high-read applications. The cost is extra storage and the need to keep copies consistent through ETL, triggers or application logic. Use it when read performance matters more than write simplicity.

Join three tables to get total sales by product category.

JoinsMedium

Show answer

SELECT cat.category_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id GROUP BY cat.category_name ORDER BY revenue DESC; Join from the most detailed table outward and aggregate at the end.

What is normalisation and why is it needed?

Show answer

Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.

What is the difference between MIN/MAX on numbers and on strings or dates?

Show answer

MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.

What is a join and what types of joins exist?

JoinsEasy

Show answer

A join combines rows from two or more tables based on a related column. INNER JOIN returns matching rows only. LEFT JOIN returns all rows from the left table with matches or NULLs. RIGHT JOIN does the opposite. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A SELF JOIN joins a table to itself, such as employees and managers.

How would you design a database for an online food delivery app at a high level?