Snowflake Interview Questions

1. Background: In the realm of data management, Snowflake processes vast amounts of structured and semi-structured data. Efficient data retrieval and organization from various sources are paramount. A binary tree traversal in level order could facilitate optimizations in query parsing and execution plans.
2. Problem statement: Write a function that performs a level-order traversal of a binary tree. The function should return the values of the nodes in level-order, where each level is processed from left to right.
3. Function/class signature:
- def level_order_traversal(root: Optional[TreeNode]) -> List[List[int]]:
4. Example 1:
- Input: root = [3,9,20,null,null,15,7]
- Output: [[3],[9,20],[15,7]]
- Explanation: The levels are traversed starting from the root (3), then moving to the next level (9 and 20), and finally to the last level (15 and 7).
5. Example 2:
- Input: root = [1]
- Output: [[1]]
- Explanation: Single-node level traversal.
6. Constraints:
- The tree may contain up to 1000 nodes.
- Node values will be integers within the range of [-1000, 1000].

Background: Snowflake often handles complex data processing tasks that involve understanding relationships and dependencies among courses in educational data sets. Managing these efficiently is crucial for query optimization and resource allocation.
Problem statement: You need to design a function that takes a list of courses and their dependencies, returning a list of courses in a valid order. If there is a cycle in the dependencies, return an empty list. The input will be a list of pairs, where each pair represents a prerequisite relationship, such as (course_a, course_b) meaning course_b is dependent on course_a.
Function/class signature:

• def course_order(dependencies: List[Tuple[str, str]]) -> List[str]:

Example 1:

• Input: [('CS101', 'CS102'), ('CS102', 'CS103'), ('CS101', 'CS104')]


• Output: ['CS101', 'CS102', 'CS103', 'CS104']


• Explanation: CS101 must be taken before CS102, which must be taken before CS103.

Example 2:

• Input: [('CS101', 'CS102'), ('CS102', 'CS101')]


• Output: []


• Explanation: There is a cycle between CS101 and CS102, so valid ordering is impossible.

Constraints:

• The number of nodes (courses) will be at most 10^4.


• The number of dependencies will be at most 10^4.


• Course identifiers are unique strings.

Background: Snowflake's architecture requires efficient scheduling of tasks that may depend on each other. Detecting cycles in dependencies is crucial for ensuring a straightforward execution order to optimize resource utilization.
Problem statement: Given a list of pairs where each pair (a, b) indicates that course a must be completed before course b, determine if you can finish all courses. If there are cycles in the dependencies, it is impossible to finish. Return true if it's possible to finish all courses, or false otherwise.
Function/class signature: def can_finish(num_courses: int, prerequisites: List[Tuple[int, int]]) -> bool
Example 1:
Input: num_courses = 2, prerequisites = [(1, 0)]
Output: True
Explanation: There are two courses and a prerequisite that allows for one course to be completed before the other, so it's possible to finish.
Example 2:
Input: num_courses = 2, prerequisites = [(1, 0), (0, 1)]
Output: False
Explanation: The prerequisites create a cycle between the two courses, making it impossible to complete.
Constraints:

• 1 <= num_courses <= 10^5


• 0 <= prerequisites.length <= 10^5


• prerequisites[i] is a pair of distinct integers in the range [0, num_courses - 1].

1. Background: In data-centric organizations like Snowflake, understanding course dependencies is crucial for optimizing task execution. A course can depend on another, and if not managed correctly, it might lead to cyclic dependencies that can freeze or crash a system.
2. Problem statement: You are tasked with implementing a function that determines if it is possible to finish all courses given their prerequisites. Each course is represented by an integer, and prerequisites are pairs of courses (where the second course must be completed before the first). Your function should return true if all courses can be finished, and false if there's a cycle.
3. Function/class signature:
- def can_finish(num_courses: int, prerequisites: List[List[int]]) -> bool:
4. Example 1:
- Input: num_courses = 2, prerequisites = [[1, 0]]
- Output: True
- Explanation: There are 2 courses, and you can finish course 0 first and then course 1.
5. Example 2:
- Input: num_courses = 2, prerequisites = [[1, 0], [0, 1]]
- Output: False
- Explanation: There is a cycle: you need to finish course 0 to finish course 1, but to finish course 0, you need to finish course 1.
6. Constraints:
- 1 <= num_courses <= 2000
- 0 <= prerequisites.length <= 5000
- prerequisites[i].length == 2
- 0 <= prerequisites[i][0], prerequisites[i][1] < num_courses

Background: In Snowflake's architecture, managing dependencies and execution order for various tasks is crucial for ensuring workflows are efficient and avoid execution delays. This problem relates to the backend logic that coordinates job scheduling and execution.
Problem statement: You need to determine if it is possible to complete all courses given a list of prerequisite pairs, where a pair [a, b] means you must complete course b before course a. If there is a cycle in the prerequisite graph, it's impossible to complete all courses. Implement a function to determine if all courses can be completed.
Function signature: def can_complete_courses(num_courses: int, prerequisites: List[List[int]]) -> bool:
Example 1:
Input: num_courses = 2, prerequisites = [[0, 1]]
Output: True
Explanation: You can take course 1 and then course 0.
Example 2:
Input: num_courses = 2, prerequisites = [[0, 1], [1, 0]]
Output: False
Explanation: There is a cycle between course 0 and course 1.
Constraints:

• 1 <= num_courses <= 2000


• 0 <= prerequisites.length <= 5000


• prerequisites[i].length == 2


• All course indices are in the range [0, num_courses - 1].

Background: In Snowflake's data warehousing service, ensuring data integrity is critical. This task involves detecting duplicate records that may arise during data ingestion or processing.
Problem statement: You are provided with a table named sales_data that contains the following columns: id, customer_id, sales_amount, and sales_date. Your goal is to write a SQL query that identifies all customer_ids with duplicate sales records. A customer is considered to be a duplicate if they have multiple entries with identical sales_date and sales_amount values.
Function/class signature:

• SELECT customer_id FROM sales_data GROUP BY customer_id HAVING COUNT(*) > 1;

Example 1:
Input:

| id | customer_id | sales_amount | sales_date   |
|----|-------------|---------------|---------------|
| 1  | 101         | 200           | 2021-09-01    |
| 2  | 102         | 150           | 2021-09-01    |
| 3  | 101         | 200           | 2021-09-01    |
| 4  | 103         | 300           | 2021-09-02    |

Output:

| customer_id |
|-------------|
| 101         |

Explanation: Customer_id 101 has duplicate entries on 2021-09-01 with the same sales amount of 200.
Example 2:
Input:

| id | customer_id | sales_amount | sales_date   |
|----|-------------|---------------|---------------|
| 5  | 104         | 400           | 2021-09-01    |
| 6  | 104         | 400           | 2021-09-01    |
| 7  | 105         | 150           | 2021-09-02    |

Output:

| customer_id |
|-------------|
| 104         |

Constraints:

• The table may contain up to 1,000,000 records.


• customer_id is a positive integer.


• sales_amount is a monetary value that can be decimal.


• sales_date is in YYYY-MM-DD format.