zoox data engineer sql technical interview insights

Zoox Data Engineer 第一轮技术店面，一小时SQL question

题目很 straightforward， 三道题，完全基于同样的Schema.

面试官: Bharath Chandra (Senior Data Engineer), 有点口音，但是人很nice, 有一些小的syntax 语法可以 google 查，只要logic是对的， 也会给提示，一小时我大概昨晚用了不到40分钟， 剩下时间就跟面试官随便聊聊接下来的面试内容, 和DE 具体工作内容, 还有公司文化一些内容， 整体还是满轻松的。

CREDIT CARD TRANSACTIONS

Schema:

transactions table

+-----------------------+-----------+----------------------------------------+

|        column         |   type    |              description               |

+------------------...

Zoox Data Engineer 第一轮技术店面，一小时SQL question

题目很 straightforward， 三道题，完全基于同样的Schema.

面试官: Bharath Chandra (Senior Data Engineer), 有点口音，但是人很nice, 有一些小的syntax 语法可以 google 查，只要logic是对的， 也会给提示，一小时我大概昨晚用了不到40分钟， 剩下时间就跟面试官随便聊聊接下来的面试内容, 和DE 具体工作内容, 还有公司文化一些内容， 整体还是满轻松的。

CREDIT CARD TRANSACTIONS

Schema:

transactions table

+-----------------------+-----------+----------------------------------------+

|        column         |   type    |              description               |

+-----------------------+-----------+----------------------------------------+

| transaction_id        | integer   | unique ID for a transaction            |

| user_id               | integer   | unique ID for a user (customer)        |

| vendor_id             | integer   | unique ID for a vendor                 |

| transaction_time      | timestamp | when the transaction was recorded      |

| transaction_dollars   | numeric   | dollar amount of the transaction       |

| transaction_type      | text      | PURCHASE, REFUND, etc.                 |

| refund_transaction_id | integer   | if transaction_type = REFUND,          |

|                       |           | transaction_id of PURCHASE transaction |

+-----------------------+-----------+----------------------------------------+

Schema:

vendors table

+----------------+---------+----------------------------------+

|     column     |  type   |           description            |

+----------------+---------+----------------------------------+

| vendor_id      | integer | unique ID for a vendor           |

| city           | text    | city where the vendor is located |

| state_province | text    | state or province where the      |

|                |         | vendor is located                |

| country        | text    | two-letter country code          |

+----------------+---------+----------------------------------+

Question 1:

Find the top 3 vendors by total dollars charged per vendor in the last 2 years.

Sample answer:

vendor_id | total_dollars_charged

-----------+-----------------------

3107 |                603.18

3997 |                600.00

3140 |                569.00

SELECT vendor_id, SUM(transaction_dollars) AS total_dollars_charged

FROM transactions

WHERE transaction_type = 'PURCHASE'

AND transaction_time >= CURRENT_DATE - INTERVAL '2 years'

GROUP BY 1

ORDER BY 2 DESC

LIMIT 3

Question 2:

What are data checks/validations that can be added to the transactions

and vendors tables? What potential edge-cases and/or anomalies can you

check for?

Can you provide an example SQL query?

这一题是开放式的，面试官要我一共给了5个DQ check, 然后选其中两个写对应的query. 大体思路可以从primary key duplication 和 anomalies value 角度入手， 也可以想想business use cases.

1. check in the transaction table, the primary key should be transaction_id, and it cannot have duplicated value, make sure it is unique id in this table.

SELECT transaction_id

FROM transactions

GROUP BY 1

HAVING COUNT(*) > 1

1. make sure the refund action always happened after the transaction, meaning the transaction_time for refund has to be later than the previous transaction.

2. check the refund amount, make sure it cannot go beyond the original transaction amounts.

3. check the transaction per vendor, make sure at least one transaction happend per each vendor.

SELECT A.vendor_id, COUNT(B.transaction_id) AS total_trasactions

FROM vendors A LEFT JOIN transactions B

ON A.vendor_id = B.vendor_id

GROUP BY 1

HAVING COUNT(B.transaction_id) = 0

1. Check the total transactions amount per each vendor, set up threshold to prevent the fraud actions. if there are spikes happened.

WITH sum_total_transaction AS (

SELECT vendor_id, SUM(transaction_dollars) AS total_dollars_charged

FROM transactions

WHERE transaction_type = 'PURCHASE'

AND transaction_time >= CURRENT_DATE - INTERVAL '2 years'

GROUP BY 1

),

rk_total AS (

SELECT A.state_province, A.country, A.vendor_id, B.total_dollars_charged, DENSE_RANK() OVER(PARTITION BY A.state_province, A.country ORDER BY B.total_dollars_charged DESC) AS rk

FROM vendors A RIGHT JOIN sum_total_transaction B

ON A.vendor_id = B.vendor_id

)

SELECT state_province, country, vendor_id, total_dollars_charged AS total_dollars, rk AS vendor_rank

FROM rk_total

WHERE rk <= 3

ORDER BY state_province, vendor_rank