CareerByteCode
ByteBlogby CareerByteCode

SQL Interview Questions Practised on BigQuery

What you build: 3 small BigQuery tables and 25 classic SQL interview questions answered with real queries and results, plus the NOT IN NULL trap, two classic errors and the cost of SELECT *.

SQL Interview Questions Practised on BigQuery

✍️ Translate this blog into Hindi, Tamil or Telugu

SQL is in almost every data, cloud and backend interview, and the questions repeat: second highest salary, customers with no orders, top N per group, running totals, duplicates, NULL traps. Reading answers is not enough. In this lab you build three small tables in BigQuery and answer 25 classic interview questions with real queries and real results, plus the two errors and the one silent trap that interviewers love to ask about. Every answer below was run, and the result table is the real one.

Who it is for: students and job seekers preparing for data analyst, data engineer, cloud or backend interviews. Time: about 1.5 hours. Cost: free. BigQuery gives 1 TB of queries and 10 GB of storage free every month; this whole lab reads a few MB.

Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every result is the real one we got.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: how to use this lab

SQL interview map
Filter and group, join, rank with window functions, clean the data, then handle time and NULLs

The 25 questions are grouped by the skill they test:

GroupQuestionsThe idea interviewers check
BasicsQ1 to Q3WHERE, ORDER BY, the three kinds of COUNT, WHERE vs HAVING
JoinsQ4 to Q7INNER vs LEFT, anti-join, self join
RankingQ8 to Q11second highest, ROW_NUMBER / RANK / DENSE_RANK, top N per group
CleaningQ12, Q13finding and removing duplicates
TimeQ14 to Q16, Q24months, running totals, LAG, gaps and islands
LogicQ17 to Q23CASE, pivot, NULL, UNION, subquery vs CTE
CostQ25why SELECT * costs more in BigQuery

Try each question yourself first, then run the answer. Real-life example: like a driving test, reading the rule book helps, but you pass by driving.

Words you will meet: a window function (... OVER (...)) calculates across a group of rows but keeps every row, unlike GROUP BY, which squeezes rows into one. PARTITION BY splits the window into groups. QUALIFY filters on a window function, like HAVING does for GROUP BY. A CTE is a named subquery written with WITH.

Before you start

  1. A Google Cloud account (the free trial works) and a project.
  2. Open Cloud Shell (the >_ icon at the top right of the console). It already has the bq tool. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com

You can also paste each SQL query into the BigQuery console (BigQuery > SQL workspace) instead of using ./q.sh.

Part B: the lab

Step 1: a dataset and a helper script

Why: a dataset is a folder for tables. q.sh saves typing: ./q.sh "SQL" runs one query and prints the result as a table.

# A dataset and a helper script: ./q.sh "SQL" runs one query in BigQuery and prints a table
bq --location=US mk --dataset --description "SQL interview practice" interview 2>&1 | tail -1
printf '#!/bin/bash\nbq query --quiet --use_legacy_sql=false --format=pretty "$1"\n' > q.sh && chmod +x q.sh && cat q.sh
Dataset 'your-project-id:interview' successfully created.
#!/bin/bash
bq query --quiet --use_legacy_sql=false --format=pretty "$1"

Step 2: three small tables

Why: small tables let you check every answer by eye. employees has managers (for the self join) and a salary tie (for ranking). customers has one customer with no orders and one with no city. orders has one duplicated row and one order with no customer, on purpose.

# Three small tables: employees, customers and orders (with a few NULLs and a duplicate on purpose)
./q.sh "CREATE OR REPLACE TABLE interview.employees AS SELECT * FROM UNNEST([
 STRUCT(1 AS id, 'Asha' AS name, 'Engineering' AS dept, CAST(NULL AS INT64) AS manager_id, 250000 AS salary, DATE '2019-04-01' AS hired),
 (2, 'Ravi', 'Engineering', 1, 180000, DATE '2020-06-15'), (3, 'Meera', 'Engineering', 1, 180000, DATE '2021-01-10'),
 (4, 'John', 'Engineering', 2, 120000, DATE '2023-03-01'), (5, 'Priya', 'Sales', 1, 150000, DATE '2020-09-01'),
 (6, 'Karan', 'Sales', 5, 95000, DATE '2022-02-20'), (7, 'Divya', 'Sales', 5, 160000, DATE '2021-07-07'),
 (8, 'Sam', 'Support', 5, 70000, DATE '2024-01-15'), (9, 'Neha', 'Support', 8, 72000, DATE '2024-05-05')])"
./q.sh "CREATE OR REPLACE TABLE interview.customers AS SELECT * FROM UNNEST([
 STRUCT(101 AS customer_id, 'Asha Traders' AS name, 'Chennai' AS city), (102, 'Bright Books', 'Pune'),
 (103, 'City Mart', 'Chennai'), (104, 'Delta Foods', CAST(NULL AS STRING)), (105, 'Echo Labs', 'Kochi')])"
./q.sh "CREATE OR REPLACE TABLE interview.orders AS SELECT * FROM UNNEST([
 STRUCT(1 AS order_id, 101 AS customer_id, DATE '2026-01-03' AS order_date, 1200 AS amount, 'delivered' AS status),
 (2, 101, DATE '2026-01-04', 800, 'delivered'), (3, 102, DATE '2026-01-04', 450, 'cancelled'),
 (4, 103, DATE '2026-01-05', 3000, 'delivered'), (5, 101, DATE '2026-01-05', 200, 'delivered'),
 (6, 102, DATE '2026-02-10', 1500, 'delivered'), (7, 103, DATE '2026-02-11', 700, 'returned'),
 (8, 101, DATE '2026-02-14', 900, 'delivered'), (9, 105, DATE '2026-03-01', 2500, 'delivered'),
 (10, 103, DATE '2026-03-02', 600, 'delivered'), (10, 103, DATE '2026-03-02', 600, 'delivered'),
 (11, CAST(NULL AS INT64), DATE '2026-03-03', 300, 'delivered')])"
./q.sh "SELECT 'employees' AS t, COUNT(*) AS n_rows FROM interview.employees UNION ALL SELECT 'customers', COUNT(*) FROM interview.customers UNION ALL SELECT 'orders', COUNT(*) FROM interview.orders"
Created your-project-id.interview.employees

Created your-project-id.interview.customers

Created your-project-id.interview.orders

+-----------+--------+
|     t     | n_rows |
+-----------+--------+
| employees |      9 |
| customers |      5 |
| orders    |     12 |
+-----------+--------+

Q1 to Q3: filter, count and group

Q1. Sales people by salary, highest first. Q2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)? Q3. Average salary per department for people hired since 2020, only for departments with at least 2 such people.

# Q1 filter and sort, Q2 COUNT(*) vs COUNT(col) vs COUNT(DISTINCT), Q3 WHERE vs HAVING
./q.sh "SELECT name, dept, salary FROM interview.employees WHERE dept = 'Sales' ORDER BY salary DESC"
./q.sh "SELECT COUNT(*) AS all_rows, COUNT(customer_id) AS with_customer, COUNT(DISTINCT customer_id) AS distinct_customers FROM interview.orders"
./q.sh "SELECT dept, COUNT(*) AS people, AVG(salary) AS avg_salary FROM interview.employees WHERE hired >= '2020-01-01' GROUP BY dept HAVING COUNT(*) >= 2 ORDER BY dept"
+-------+-------+--------+
| name  | dept  | salary |
+-------+-------+--------+
| Divya | Sales | 160000 |
| Priya | Sales | 150000 |
| Karan | Sales |  95000 |
+-------+-------+--------+
+----------+---------------+--------------------+
| all_rows | with_customer | distinct_customers |
+----------+---------------+--------------------+
|       12 |            11 |                  4 |
+----------+---------------+--------------------+
+-------------+--------+------------+
|    dept     | people | avg_salary |
+-------------+--------+------------+
| Engineering |      3 |   160000.0 |
| Sales       |      3 |   135000.0 |
| Support     |      2 |    71000.0 |
+-------------+--------+------------+
Basics
Q1 to Q3: a sorted filter, the three COUNTs (12, 11, 4) and GROUP BY with HAVING
  • COUNT(*) counts rows (12). COUNT(customer_id) skips NULLs (11: one order has no customer). COUNT(DISTINCT customer_id) counts different values (4).
  • WHERE filters rows before grouping (hired since 2020). HAVING filters groups after grouping (at least 2 people). Asha (hired 2019) is not in the Engineering average.

Q4 and Q5: INNER vs LEFT JOIN, and customers with no orders

Q4. Join orders to customers. Why does INNER JOIN give 11 rows and LEFT JOIN 12? Q5. Which customers have never ordered?

# Q4 INNER vs LEFT JOIN, Q5 customers with no orders (anti-join)
./q.sh "SELECT 'inner' AS join_type, COUNT(*) AS n_rows FROM interview.orders o JOIN interview.customers c USING (customer_id) UNION ALL SELECT 'left', COUNT(*) FROM interview.orders o LEFT JOIN interview.customers c USING (customer_id)"
./q.sh "SELECT c.customer_id, c.name FROM interview.customers c LEFT JOIN interview.orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL"
+-----------+--------+
| join_type | n_rows |
+-----------+--------+
| inner     |     11 |
| left      |     12 |
+-----------+--------+
+-------------+-------------+
| customer_id |    name     |
+-------------+-------------+
|         104 | Delta Foods |
+-------------+-------------+

INNER JOIN keeps only rows with a match on both sides; the order with no customer is dropped. LEFT JOIN keeps every row of the left table. For Q5, a LEFT JOIN from customers plus WHERE o.order_id IS NULL (an anti-join) finds Delta Foods.

Break it: the NOT IN and NULL trap

The same question as Q5, written with NOT IN, the way many people first try it:

# Break it: the NOT IN + NULL trap. The same question with NOT IN returns NOTHING, because orders has a NULL customer_id
./q.sh "SELECT customer_id, name FROM interview.customers WHERE customer_id NOT IN (SELECT customer_id FROM interview.orders)"
echo "(no rows above)"
./q.sh "SELECT customer_id, name FROM interview.customers c WHERE NOT EXISTS (SELECT 1 FROM interview.orders o WHERE o.customer_id = c.customer_id)"
(no rows above)
+-------------+-------------+
| customer_id |    name     |
+-------------+-------------+
|         104 | Delta Foods |
+-------------+-------------+

NOT IN returned nothing: no error, no warning, just an empty result. The orders table has one NULL customer_id, and x NOT IN (..., NULL) can never be TRUE (comparing with NULL gives "unknown"). NOT EXISTS (or the LEFT JOIN above) gives the right answer, Delta Foods. Rule: never use NOT IN with a subquery whose column may contain NULL.

Q6 and Q7: the self join

Q6. List every employee with their manager. Q7. Who earns more than their own manager?

# Q6 each employee with their manager (self join), Q7 employees who earn more than their manager
./q.sh "SELECT e.name AS employee, COALESCE(m.name, '(none)') AS manager FROM interview.employees e LEFT JOIN interview.employees m ON e.manager_id = m.id ORDER BY e.id"
./q.sh "SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary FROM interview.employees e JOIN interview.employees m ON e.manager_id = m.id WHERE e.salary > m.salary"
+----------+---------+
| employee | manager |
+----------+---------+
| Asha     | (none)  |
| Ravi     | Asha    |
| Meera    | Asha    |
| John     | Ravi    |
| Priya    | Asha    |
| Karan    | Priya   |
| Divya    | Priya   |
| Sam      | Priya   |
| Neha     | Sam     |
+----------+---------+
+-------+--------+---------+----------------+
| name  | salary | manager | manager_salary |
+-------+--------+---------+----------------+
| Neha  |  72000 | Sam     |          70000 |
| Divya | 160000 | Priya   |         150000 |
+-------+--------+---------+----------------+

A self join joins a table to itself with two aliases (e for the employee, m for the manager). LEFT JOIN keeps Asha, who has no manager.

Q8 and Q9: second highest salary, and the three ranking functions

Q8. The second highest salary (two ways). Q9. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

# Q8 second highest salary (two ways), Q9 RANK vs DENSE_RANK vs ROW_NUMBER on a tie
./q.sh "SELECT MAX(salary) AS second_highest FROM interview.employees WHERE salary < (SELECT MAX(salary) FROM interview.employees)"
./q.sh "SELECT DISTINCT salary AS second_highest FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM interview.employees) WHERE r = 2"
./q.sh "SELECT name, salary, ROW_NUMBER() OVER w AS row_number, RANK() OVER w AS rank, DENSE_RANK() OVER w AS dense_rank FROM interview.employees WHERE dept = 'Engineering' WINDOW w AS (ORDER BY salary DESC)"
+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+
+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+
+-------+--------+------------+------+------------+
| name  | salary | row_number | rank | dense_rank |
+-------+--------+------------+------+------------+
| Asha  | 250000 |          1 |    1 |          1 |
| Ravi  | 180000 |          2 |    2 |          2 |
| Meera | 180000 |          3 |    2 |          2 |
| John  | 120000 |          4 |    4 |          3 |
+-------+--------+------------+------+------------+
Self join and ranks
Q6 to Q9: managers by self join, two who out-earn their manager, second highest = 180000, and the three ranks on a tie

Ravi and Meera tie at 180,000. ROW_NUMBER gives them 2 and 3 anyway (every row its own number). RANK gives both 2 and skips 3, so John is 4. DENSE_RANK gives both 2 and does not skip, so John is 3. That is why "the Nth highest salary" uses DENSE_RANK.

Q10 and Q11: top 1 per group, and percent of the group

Q10. The highest paid person in each department. Q11. Each salary as a percent of its department's total.

# Q10 highest paid person in each department (top N per group with QUALIFY), Q11 each salary as a percent of its department
./q.sh "SELECT dept, name, salary FROM interview.employees WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1 ORDER BY dept"
./q.sh "SELECT dept, name, salary, ROUND(100 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept FROM interview.employees ORDER BY dept, salary DESC"
+-------------+-------+--------+
|    dept     | name  | salary |
+-------------+-------+--------+
| Engineering | Asha  | 250000 |
| Sales       | Divya | 160000 |
| Support     | Neha  |  72000 |
+-------------+-------+--------+
+-------------+-------+--------+-------------+
|    dept     | name  | salary | pct_of_dept |
+-------------+-------+--------+-------------+
| Engineering | Asha  | 250000 |        34.2 |
| Engineering | Ravi  | 180000 |        24.7 |
| Engineering | Meera | 180000 |        24.7 |
| Engineering | John  | 120000 |        16.4 |
| Sales       | Divya | 160000 |        39.5 |
| Sales       | Priya | 150000 |        37.0 |
| Sales       | Karan |  95000 |        23.5 |
| Support     | Neha  |  72000 |        50.7 |
| Support     | Sam   |  70000 |        49.3 |
+-------------+-------+--------+-------------+

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) numbers people inside each department, and QUALIFY ... = 1 keeps the first. Change = 1 to <= 3 for top 3. In Q11, SUM(salary) OVER (PARTITION BY dept) is the department total on every row, so dividing gives the share without a second query.

Q12 and Q13: find and remove duplicates

Q12. Find duplicate orders. Q13. Remove them and keep one copy.

# Q12 find duplicate rows, Q13 remove them and keep one copy
./q.sh "SELECT order_id, COUNT(*) AS copies FROM interview.orders GROUP BY order_id HAVING COUNT(*) > 1"
./q.sh "CREATE OR REPLACE TABLE interview.orders_clean AS SELECT * FROM interview.orders WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date) = 1"
./q.sh "SELECT (SELECT COUNT(*) FROM interview.orders) AS before_rows, (SELECT COUNT(*) FROM interview.orders_clean) AS after_rows"
+----------+--------+
| order_id | copies |
+----------+--------+
|       10 |      2 |
+----------+--------+
Created your-project-id.interview.orders_clean

+-------------+------------+
| before_rows | after_rows |
+-------------+------------+
|          12 |         11 |
+-------------+------------+

GROUP BY ... HAVING COUNT(*) > 1 finds them. To keep one, number the copies with ROW_NUMBER() OVER (PARTITION BY order_id ...) and keep number 1 in a new table: 12 rows become 11. The rest of the lab uses orders_clean.

Q14 to Q16: revenue per month, running total, change vs last month

# Q14 revenue per month, Q15 running total, Q16 month-over-month change with LAG
./q.sh "WITH m AS (SELECT DATE_TRUNC(order_date, MONTH) AS month, SUM(amount) AS revenue FROM interview.orders_clean WHERE status = 'delivered' GROUP BY month)
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total, revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_last_month FROM m ORDER BY month"
+------------+---------+---------------+----------------------+
|   month    | revenue | running_total | change_vs_last_month |
+------------+---------+---------------+----------------------+
| 2026-01-01 |    5200 |          5200 |                 NULL |
| 2026-02-01 |    2400 |          7600 |                -2800 |
| 2026-03-01 |    3400 |         11000 |                 1000 |
+------------+---------+---------------+----------------------+
Duplicates and time
Q10 to Q16: top per group, percent of department, duplicates removed, revenue per month with a running total and LAG

DATE_TRUNC(order_date, MONTH) turns every date into the first day of its month. SUM(...) OVER (ORDER BY month) adds up month by month (a running total). LAG(revenue) reads the previous row, so the change in February is 2,400 minus 5,200 = -2,800. January has no previous month, so the change is NULL.

Q17 and Q18: CASE buckets and a pivot

Q17. Label orders small, medium or large and count them. Q18. One row per customer with one column per status.

# Q17 CASE: bucket the orders, Q18 pivot: one column per status
./q.sh "SELECT CASE WHEN amount >= 2000 THEN 'large' WHEN amount >= 700 THEN 'medium' ELSE 'small' END AS size, COUNT(*) AS orders FROM interview.orders_clean GROUP BY size ORDER BY orders DESC"
./q.sh "SELECT customer_id, COUNTIF(status = 'delivered') AS delivered, COUNTIF(status = 'cancelled') AS cancelled, COUNTIF(status = 'returned') AS returned FROM interview.orders_clean WHERE customer_id IS NOT NULL GROUP BY customer_id ORDER BY customer_id"
+--------+--------+
|  size  | orders |
+--------+--------+
| medium |      5 |
| small  |      4 |
| large  |      2 |
+--------+--------+
+-------------+-----------+-----------+----------+
| customer_id | delivered | cancelled | returned |
+-------------+-----------+-----------+----------+
|         101 |         4 |         0 |        0 |
|         102 |         1 |         1 |        0 |
|         103 |         2 |         0 |        1 |
|         105 |         1 |         0 |        0 |
+-------------+-----------+-----------+----------+

CASE WHEN is SQL's if-else. COUNTIF(condition) (in other databases SUM(CASE WHEN ... THEN 1 ELSE 0 END)) turns rows into columns, which is what a pivot is.

Q19 and Q20: NULL, and UNION vs UNION ALL

🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →

Q19. How do you find customers with no city? Q20. What is the difference between UNION ALL and UNION DISTINCT?

# Q19 NULL is not equal to anything (not even NULL), Q20 UNION vs UNION ALL
./q.sh "SELECT COUNTIF(city = NULL) AS equals_null, COUNTIF(city IS NULL) AS is_null, COUNT(*) AS all_rows FROM interview.customers"
./q.sh "SELECT name, COALESCE(city, 'unknown') AS city FROM interview.customers WHERE city IS NULL OR city = 'Kochi'"
./q.sh "SELECT (SELECT COUNT(*) FROM (SELECT city FROM interview.customers UNION ALL SELECT city FROM interview.customers)) AS union_all_rows, (SELECT COUNT(*) FROM (SELECT city FROM interview.customers UNION DISTINCT SELECT city FROM interview.customers)) AS union_distinct_rows"
Error in query string: Error processing job 'your-project-id:bqjob_r7ad538d7b6a2ae51_000001a1f0939cd5_1': Operands of = cannot be
literal NULL at [1:23]
+-------------+---------+
|    name     |  city   |
+-------------+---------+
| Echo Labs   | Kochi   |
| Delta Foods | unknown |
+-------------+---------+
+----------------+---------------------+
| union_all_rows | union_distinct_rows |
+----------------+---------------------+
|             10 |                   4 |
+----------------+---------------------+

Interesting: BigQuery refuses city = NULL outright (Operands of = cannot be literal NULL). Many other databases accept it and just return no rows, which is worse. Either way the answer is IS NULL, and COALESCE(city, 'unknown') replaces a NULL with a value. UNION ALL keeps every row (5 + 5 = 10); UNION DISTINCT (written just UNION elsewhere) removes duplicates, leaving the 4 different cities (NULL counts as one value here).

Q21 to Q23: subquery, CTE, first order per customer

Q21. Which departments pay above the company average? Q22. The same with a CTE. Q23. Each customer's first order date.

# Q21 departments that pay above the company average (subquery), Q22 the same with a CTE, Q23 first order of each customer
./q.sh "SELECT dept, ROUND(AVG(salary)) AS avg_salary FROM interview.employees GROUP BY dept HAVING AVG(salary) > (SELECT AVG(salary) FROM interview.employees)"
./q.sh "WITH company AS (SELECT AVG(salary) AS avg_all FROM interview.employees), d AS (SELECT dept, AVG(salary) AS avg_dept FROM interview.employees GROUP BY dept)
SELECT d.dept, ROUND(d.avg_dept) AS avg_dept, ROUND(company.avg_all) AS avg_all FROM d CROSS JOIN company WHERE d.avg_dept > company.avg_all"
./q.sh "SELECT customer_id, MIN(order_date) AS first_order, COUNT(*) AS orders FROM interview.orders_clean WHERE customer_id IS NOT NULL GROUP BY customer_id ORDER BY first_order"
+-------------+------------+
|    dept     | avg_salary |
+-------------+------------+
| Engineering |   182500.0 |
+-------------+------------+
+-------------+----------+----------+
|    dept     | avg_dept | avg_all  |
+-------------+----------+----------+
| Engineering | 182500.0 | 141889.0 |
+-------------+----------+----------+
+-------------+-------------+--------+
| customer_id | first_order | orders |
+-------------+-------------+--------+
|         101 |  2026-01-03 |      4 |
|         102 |  2026-01-04 |      2 |
|         103 |  2026-01-05 |      3 |
|         105 |  2026-03-01 |      1 |
+-------------+-------------+--------+

The subquery and the CTE give the same answer. A CTE (WITH name AS (...)) reads top to bottom and can be reused, which is why interviewers like to see it in long queries.

Q24: gaps and islands (days in a row)

Q24. Find the runs of consecutive days with orders.

# Q24 gaps and islands: the longest run of consecutive order days
./q.sh "WITH d AS (SELECT DISTINCT order_date FROM interview.orders_clean), g AS (SELECT order_date, DATE_SUB(order_date, INTERVAL ROW_NUMBER() OVER (ORDER BY order_date) DAY) AS grp FROM d)
SELECT MIN(order_date) AS start_day, MAX(order_date) AS end_day, COUNT(*) AS days_in_a_row FROM g GROUP BY grp HAVING COUNT(*) > 1 ORDER BY days_in_a_row DESC"
+------------+------------+---------------+
| start_day  |  end_day   | days_in_a_row |
+------------+------------+---------------+
| 2026-01-03 | 2026-01-05 |             3 |
| 2026-03-01 | 2026-03-03 |             3 |
| 2026-02-10 | 2026-02-11 |             2 |
+------------+------------+---------------+
Logic and streaks
Q17 to Q24: CASE and pivot, the NULL rule, UNION, subquery and CTE, and runs of consecutive days

The trick: subtract the row number (in days) from each date. Dates in a row all give the same result, so it becomes a group key. Three runs: 3 Jan to 5 Jan, 1 Mar to 3 Mar and 10 Feb to 11 Feb.

Break it: two classic errors

Two errors every SQL user meets, and interviewers ask you to explain:

# Break it: two errors every interviewer asks about. A column that is not grouped, and an ambiguous column name
./q.sh "SELECT dept, name, MAX(salary) FROM interview.employees GROUP BY dept" 2>&1 | tr -s '\n' ' ' | sed 's/.*_1.: //'; echo
./q.sh "SELECT customer_id, name FROM interview.orders o JOIN interview.customers c ON o.customer_id = c.customer_id" 2>&1 | tr -s '\n' ' ' | sed 's/.*_1.: //'; echo
SELECT list expression
 references column name which is neither grouped nor aggregated at [1:14]
 
Column name customer_id
 is ambiguous at [1:8]
  1. Not grouped: name is in SELECT but not in GROUP BY and not inside an aggregate. Which name should BigQuery show for a department of 4 people? Fix: group by it too, or use a window function (Q10).
  2. Ambiguous column: customer_id exists in both tables. Fix: say which one, o.customer_id, or join with USING (customer_id) as in Q4.

Q25: why SELECT * costs more in BigQuery

Why: BigQuery stores each column separately and charges for the bytes it reads. A dry run shows the bytes without running the query or costing anything.

# Q25 why SELECT * costs more: dry runs on a public table (no data read, no cost)
bq query --use_legacy_sql=false --dry_run "SELECT * FROM \`bigquery-public-data.samples.shakespeare\`" 2>&1 | grep -o "process [0-9]* bytes"
bq query --use_legacy_sql=false --dry_run "SELECT word FROM \`bigquery-public-data.samples.shakespeare\`" 2>&1 | grep -o "process [0-9]* bytes"
bq query --use_legacy_sql=false --dry_run "SELECT word FROM \`bigquery-public-data.samples.shakespeare\` LIMIT 10" 2>&1 | grep -o "process [0-9]* bytes"
process 6432064 bytes
process 1332943 bytes
process 1332943 bytes
Errors and cost
Two classic errors explained, and dry runs: SELECT * reads 6.4 MB, one column 1.3 MB, and LIMIT does not reduce it

SELECT * reads 6.4 MB, one column 1.3 MB. And LIMIT 10 reads the same 1.3 MB: it limits the rows you get back, not the data scanned. On tables of terabytes, this is the most expensive interview answer to get wrong.

POC: prove it works

  • 25 questions answered with real queries and real results on your own tables.
  • The NOT IN trap returned no rows; NOT EXISTS returned the right customer.
  • ROW_NUMBER, RANK and DENSE_RANK shown side by side on a real tie.
  • A duplicate removed (12 rows to 11), a running total and a month-over-month change.
  • Two classic errors explained from BigQuery's own messages, and dry runs proving SELECT * and LIMIT cost.

Quiz task: check what you learned

  1. Find the third highest salary. *(Answer: Q8's second form with WHERE r = 3; it returns 120000 here.)*
  2. Average order amount per city, cities with more than 1 order only. *(Answer: join orders to customers, GROUP BY city, HAVING COUNT(*) > 1.)*
  3. Why can NOT IN return no rows? *(Answer: a NULL in the subquery makes every comparison unknown. Use NOT EXISTS.)*
  4. Rank orders by amount inside each customer. *(Answer: RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC).)*
  5. Does LIMIT reduce BigQuery cost? (Answer: no; select fewer columns, or filter on partitioned or clustered columns.)

Clean up (delete everything)

Why: a dataset with tables counts toward the free 10 GB of storage. bq rm -r -f -d removes the dataset and every table in it.

# Clean up: delete the dataset and every table in it
bq rm -r -f -d interview && echo "dataset interview deleted"
echo "datasets left: $(bq ls --format=json 2>/dev/null | python3 -c 'import json,sys; print(len(json.load(sys.stdin) or []))' 2>/dev/null || echo 0)"
dataset interview deleted
datasets left: 0

FAQ

Is BigQuery SQL the same as MySQL or PostgreSQL SQL? The core (SELECT, JOIN, GROUP BY, window functions) is standard and the same. Some functions differ: BigQuery has COUNTIF, QUALIFY and DATE_TRUNC(date, MONTH); PostgreSQL writes date_trunc('month', date). Interviews care about the ideas.

**Do I need WHERE TRUE before QUALIFY?** Not any more in BigQuery: QUALIFY works on its own. Some older examples (and our Q10 and Q13) add WHERE TRUE, which does no harm.

Window function or GROUP BY? GROUP BY returns one row per group. A window function keeps every row and adds the group value next to it (Q11). Use a window when you need both the detail and the total.

Subquery or CTE? Same result and usually the same speed in BigQuery. CTEs are easier to read and to reuse in the same query.

How do I practise on bigger data? BigQuery has free public datasets under bigquery-public-data (Q25 used Shakespeare's words). Always dry-run first.

Which questions come up most? In our experience: second highest salary, customers without orders, top N per group, duplicates, running totals, and NULL handling. All six are in this lab.

Troubleshooting

You seeWhyFix
SELECT list expression references column ... which is neither grouped nor aggregatedA column in SELECT is not in GROUP BYAdd it to GROUP BY, aggregate it, or use a window
Column name ... is ambiguousThe column is in both joined tablesPrefix it (o.customer_id) or use USING
Operands of = cannot be literal NULL= NULL instead of IS NULLWrite IS NULL / IS NOT NULL
NOT IN returns no rowsA NULL in the subqueryUse NOT EXISTS or filter NULLs out
Not found: DatasetWrong dataset name or projectbq ls, and check gcloud config get-value project
A query costs more than expectedSELECT *, or LIMIT thought to save costDry-run; select only the needed columns

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →Looker Studio Dashboard on BigQuery Data⏱ 14 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →