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 *.

✍️ 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

The 25 questions are grouped by the skill they test:
| Group | Questions | The idea interviewers check |
|---|---|---|
| Basics | Q1 to Q3 | WHERE, ORDER BY, the three kinds of COUNT, WHERE vs HAVING |
| Joins | Q4 to Q7 | INNER vs LEFT, anti-join, self join |
| Ranking | Q8 to Q11 | second highest, ROW_NUMBER / RANK / DENSE_RANK, top N per group |
| Cleaning | Q12, Q13 | finding and removing duplicates |
| Time | Q14 to Q16, Q24 | months, running totals, LAG, gaps and islands |
| Logic | Q17 to Q23 | CASE, pivot, NULL, UNION, subquery vs CTE |
| Cost | Q25 | why 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
- A Google Cloud account (the free trial works) and a project.
- Open Cloud Shell (the
>_icon at the top right of the console). It already has thebqtool. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.comYou 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.shDataset '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 |
+-------------+--------+------------+
COUNT(*)counts rows (12).COUNT(customer_id)skips NULLs (11: one order has no customer).COUNT(DISTINCT customer_id)counts different values (4).WHEREfilters rows before grouping (hired since 2020).HAVINGfilters 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 |
+-------+--------+------------+------+------------+
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 |
+------------+---------+---------------+----------------------+
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 |
+------------+------------+---------------+
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.: //'; echoSELECT list expression
references column name which is neither grouped nor aggregated at [1:14]
Column name customer_id
is ambiguous at [1:8]- Not grouped:
nameis inSELECTbut not inGROUP BYand 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). - Ambiguous column:
customer_idexists in both tables. Fix: say which one,o.customer_id, or join withUSING (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
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,RANKandDENSE_RANKshown 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 *andLIMITcost.
Quiz task: check what you learned
- Find the third highest salary. *(Answer: Q8's second form with
WHERE r = 3; it returns 120000 here.)* - Average order amount per city, cities with more than 1 order only. *(Answer: join orders to customers,
GROUP BY city,HAVING COUNT(*) > 1.)* - Why can
NOT INreturn no rows? *(Answer: a NULL in the subquery makes every comparison unknown. UseNOT EXISTS.)* - Rank orders by amount inside each customer. *(Answer:
RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC).)* - Does
LIMITreduce 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: 0FAQ
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 see | Why | Fix |
|---|---|---|
SELECT list expression references column ... which is neither grouped nor aggregated | A column in SELECT is not in GROUP BY | Add it to GROUP BY, aggregate it, or use a window |
Column name ... is ambiguous | The column is in both joined tables | Prefix it (o.customer_id) or use USING |
Operands of = cannot be literal NULL | = NULL instead of IS NULL | Write IS NULL / IS NOT NULL |
NOT IN returns no rows | A NULL in the subquery | Use NOT EXISTS or filter NULLs out |
Not found: Dataset | Wrong dataset name or project | bq ls, and check gcloud config get-value project |
| A query costs more than expected | SELECT *, or LIMIT thought to save cost | Dry-run; select only the needed columns |
What next
- Start with BigQuery itself: BigQuery for Beginners: Query Billions of Rows for Free.
- Use SQL on real logs: Logs to BigQuery: Analyse a Month of Logs with SQL.
- Train a model with SQL only: BigQuery ML: Train a Model with Just SQL.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet 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.
Related blogs
CareerByteCode TeamLooker Studio Dashboard on BigQuery Data
What you build: six months of shop data in BigQuery and a clean dashboard view, with two real dashboard bugs caught (a fan-out join and UTC dates), every chart number checked, and a one-click Looker S
#beginners
CareerByteCode TeamCloud SQL for PostgreSQL: Create, Connect and Back Up
What you build: a Cloud SQL for PostgreSQL 17 instance with private IP only, a database and user, real data, an on-demand backup and a real restore, plus the Auth Proxy. Every step tested.
#beginners
CareerByteCode TeamBigQuery for Beginners: Query Billions of Rows for Free
What you build: SQL on a public dataset with millions of rows, then your own sales table with a view and a daily partition, in your own Google Cloud account. Every step tested.
#beginners
CareerByteCode TeamCloud Storage Deep Dive: Classes, Lifecycle and Signed URLs
What you build: a private bucket with storage classes, a lifecycle policy, object versions and expiring signed URLs, in your own Google Cloud account. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment