Pre-Interview Technical Assessment - Hands-On Python and SQL Proficiency
These questions are designed to gauge your hands-on knowledge and practical experience in
Python programming and SQL, providing a solid foundation for our interview discussion.
Please be prepared to solve these while sharing your screen during the interview.
1. Window Functions
Example: Ranking Products by Sales This query ranks products by their total sales within
each category.
sql
SELECT
category,
product_name,
sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS sales_rank
FROM
products;
2. Common Table Expressions (CTEs)
Example: Finding the Second Highest Salary This query uses a CTE to find the second
highest salary in the employees table.
sql
WITH ranked_salaries AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM
employees
)
SELECT
salary
FROM
ranked_salaries
WHERE
rank = 2;
3. Recursive CTEs
Example: Hierarchical Data Query This query retrieves all employees and their managers
in a hierarchical structure.
sql
WITH RECURSIVE employee_hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
[Link] + 1
FROM
employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
employee_id,
employee_name,
manager_id,
level
FROM
employee_hierarchy
ORDER BY
level;
4. Subqueries
Example: Correlated Subquery This query finds all employees who earn more than the
average salary in their department.
sql
SELECT
employee_id,
employee_name,
department_id,
salary
FROM
employees e1
WHERE
salary > (
SELECT
AVG(salary)
FROM
employees e2
WHERE
e1.department_id = e2.department_id
);
5. Aggregations and Group By
Example: Monthly Sales Report This query calculates the total sales and the number of
transactions per month.
sql
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
COUNT(*) AS transaction_count,
SUM(amount) AS total_sales
FROM
sales
GROUP BY
EXTRACT(YEAR FROM sale_date),
EXTRACT(MONTH FROM sale_date)
ORDER BY
year,
month;
6. Self Joins
Example: Finding Employees with the Same Manager This query finds pairs of employees
who share the same manager.
sql
SELECT
e1.employee_name AS employee,
e2.employee_name AS colleague,
e1.manager_id
FROM
employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE
e1.employee_id != e2.employee_id;
7. Complex Joins and Filtering
Example: Orders with Product Details This query retrieves order details along with
product information and filters out orders with low quantities.
sql
SELECT
o.order_id,
o.order_date,
p.product_name,
[Link],
[Link],
[Link]
FROM
orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE
[Link] > 10;
8. Data Manipulation
Example: Updating Records Based on Another Table This query updates the salary of
employees based on the average salary of their department.
sql
UPDATE
employees e
SET
salary = salary * 1.1
WHERE
salary < (
SELECT
AVG(salary)
FROM
employees
WHERE
department_id = e.department_id
);
Python Questions -
1. Reverse Words in a Sentence
Write a program to reverse the words in a given sentence. For example, input "snow is white"
should output "white is snow".
python
def reverse_sentence(sentence):
words = [Link]()
reversed_words = words[::-1]
return ' '.join(reversed_words)
# Test
sentence = "snow is white"
print(reverse_sentence(sentence)) # Output: "white is snow"
2. Find the Longest Word in a Sentence
Write a program to find the longest word in a given sentence.
python
def find_longest_word(sentence):
words = [Link]()
longest_word = max(words, key=len)
return longest_word
# Test
sentence = "The quick brown fox jumps over the lazy dog"
print(find_longest_word(sentence)) # Output: "jumps"
3. Check if a String is a Palindrome
Write a program to check if a given string is a palindrome.
python
def is_palindrome(s):
cleaned = ''.join(filter([Link], s)).lower()
return cleaned == cleaned[::-1]
# Test
string = "A man, a plan, a canal, Panama"
print(is_palindrome(string)) # Output: True
4. Sum of Elements in a List
Write a program to find the sum of all elements in a list.
python
def sum_of_elements(lst):
return sum(lst)
# Test
numbers = [1, 2, 3, 4, 5]
print(sum_of_elements(numbers)) # Output: 15
5. Count Occurrences of Words in a String
Write a program to count the occurrences of each word in a string.
python
def count_words(s):
words = [Link]()
word_count = {}
for word in words:
word_count[word] = word_count.get(word, 0) + 1
return word_count
# Test
sentence = "the quick brown fox jumps over the lazy dog the dog barked"
print(count_words(sentence)) # Output: {'the': 3, 'quick': 1, 'brown': 1,
'fox': 1, 'jumps': 1, 'over': 1, 'lazy': 1, 'dog': 2, 'barked': 1}
6. Find Common Elements in Two Lists
Write a program to find the common elements in two lists.
python
def common_elements(list1, list2):
return list(set(list1) & set(list2))
# Test
list1 = [1, 2, 3, 4, 5]
list2 = [4, 5, 6, 7, 8]
print(common_elements(list1, list2)) # Output: [4, 5]
7. Read from a File and Count Lines
Write a program to read a file and count the number of lines in it.
python
def count_lines_in_file(file_path):
with open(file_path, 'r') as file:
lines = [Link]()
return len(lines)
# Test
# Assuming '[Link]' is a file with some text in it.
file_path = '[Link]'
print(count_lines_in_file(file_path)) # Output: (number of lines in
[Link])
8. Dictionary Merge
Write a program to merge two dictionaries.
python
def merge_dictionaries(dict1, dict2):
merged_dict = [Link]()
merged_dict.update(dict2)
return merged_dict
# Test
dict1 = {'a': 1, 'b': 2}
dict2 = {'b': 3, 'c': 4}
print(merge_dictionaries(dict1, dict2)) # Output: {'a': 1, 'b': 3, 'c': 4}
9. Prime Number Check
Write a program to check if a given number is a prime number.
python
def is_prime(n):
if n <= 1:
return False
for i in range(2, int(n**0.5) + 1):
if n % i == 0:
return False
return True
# Test
number = 29
print(is_prime(number)) # Output: True
Additional Question (If time permits otherwise Optional) - SQL
Window Functions
1. Row Number Write a query to assign a unique row number to each employee within
their department based on their hire date.
sql
SELECT name, department_id, hire_date,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY
hire_date) AS row_num
FROM employees;
2. Rank Write a query to rank employees within each department based on their salary.
sql
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
AS salary_rank
FROM employees;
3. Dense Rank Write a query to rank employees within each department based on their
salary, without gaps in the rank values.
sql
SELECT name, department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary
DESC) AS salary_dense_rank
FROM employees;
4. Lead and Lag Write a query to get each employee’s salary and the salary of the
employee hired just before them.
sql
SELECT name, hire_date, salary,
LAG(salary) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;
5. Running Total Write a query to calculate the running total of salaries for employees
ordered by their hire date.
sql
SELECT name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;
Subqueries
1. Simple Subquery Write a query to find the names of employees who earn more than
the average salary.
sql
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
2. Correlated Subquery Write a query to find employees who earn more than the
average salary in their department.
sql
SELECT name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);
3. IN Clause with Subquery Write a query to find the names of employees who work
in departments located in 'New York'.
sql
SELECT name
FROM employees
WHERE department_id IN (SELECT id
FROM departments
WHERE location = 'New York');
4. EXISTS Clause Write a query to find employees who have at least one dependent.
sql
SELECT name
FROM employees e
WHERE EXISTS (SELECT 1
FROM dependents d
WHERE d.employee_id = [Link]);
5. NOT EXISTS Clause Write a query to find employees who do not have any
dependents.
sql
SELECT name
FROM employees e
WHERE NOT EXISTS (SELECT 1
FROM dependents d
WHERE d.employee_id = [Link]);
Join Conditions
1. INNER JOIN Write a query to list all employees and their corresponding department
names.
sql
SELECT [Link] AS employee_name, [Link] AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = [Link];
2. LEFT JOIN Write a query to list all employees and their department names,
including those employees who do not belong to any department.
sql
SELECT [Link] AS employee_name, [Link] AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = [Link];
3. RIGHT JOIN Write a query to list all departments and the names of their employees,
including those departments that do not have any employees.
sql
SELECT [Link] AS department_name, [Link] AS employee_name
FROM departments d
RIGHT JOIN employees e ON e.department_id = [Link];
4. FULL OUTER JOIN Write a query to list all employees and their department
names, including those employees without departments and those departments
without employees.
sql
SELECT [Link] AS employee_name, [Link] AS department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = [Link];
5. Self JOIN Write a query to list employees along with their managers.
sql
SELECT [Link] AS employee_name, [Link] AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = [Link];
Query Performance
1. Indexes Explain how you would use indexes to improve the performance of a query
that selects employees by their last name.
sql
-- Example:
CREATE INDEX idx_last_name ON employees(last_name);
2. Query Optimization Write a query to find employees with a salary greater than the
average salary, and explain how you would optimize it.
sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Optimization: Ensure the subquery executes only once:
WITH AvgSalary AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT name, salary
FROM employees, AvgSalary
WHERE [Link] > AvgSalary.avg_salary;
3. EXPLAIN Statement Describe how you would use the EXPLAIN statement to analyze
a complex query's execution plan.
sql
EXPLAIN ANALYZE
SELECT [Link], [Link] AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = [Link];
4. Join Order Write a query to join three tables: employees, departments, and locations.
Explain the importance of join order in query optimization.
sql
SELECT [Link], [Link] AS department_name, [Link] AS location_name
FROM employees e
INNER JOIN departments d ON e.department_id = [Link]
INNER JOIN locations l ON d.location_id = [Link];
-- Explanation:
-- Join order can impact performance; generally, it's better to join
smaller tables first.
5. Partitioning Explain how partitioning can improve query performance for a table
with millions of rows.
sql
-- Example:
CREATE TABLE employees (
id INT,
name VARCHAR(100),
hire_date DATE,
department_id INT
) PARTITION BY RANGE (hire_date) (
PARTITION p0 VALUES LESS THAN ('2000-01-01'),
PARTITION p1 VALUES LESS THAN ('2010-01-01'),
PARTITION p2 VALUES LESS THAN ('2020-01-01')
);
Additional Question (If time permits otherwise Optional) - Python
Data Structures
1. Lists
o Question: Write a Python function to find the second largest number in a list.
python
def second_largest(numbers):
first, second = float('-inf'), float('-inf')
for number in numbers:
if number > first:
first, second = number, first
elif first > number > second:
second = number
return second
# Example usage:
numbers = [3, 1, 4, 1, 5, 9, 2, 6, 5]
print(second_largest(numbers)) # Output: 6
2. Dictionaries
o Question: Write a Python function to count the frequency of each word in a
given string.
python
def word_frequency(text):
words = [Link]()
frequency = {}
for word in words:
if word in frequency:
frequency[word] += 1
else:
frequency[word] = 1
return frequency
# Example usage:
text = "the quick brown fox jumps over the lazy dog the fox"
print(word_frequency(text))
# Output: {'the': 2, 'quick': 1, 'brown': 1, 'fox': 2, 'jumps':
1, 'over': 1, 'lazy': 1, 'dog': 1}
3. Sets
o Question: Write a Python function to find the intersection of two lists using
sets.
python
def intersection(list1, list2):
return list(set(list1) & set(list2))
# Example usage:
list1 = [1, 2, 3, 4, 5]
list2 = [4, 5, 6, 7, 8]
print(intersection(list1, list2)) # Output: [4, 5]
4. Tuples
o Question: Write a Python function to sort a list of tuples based on the second
element.
python
def sort_by_second_element(tuples):
return sorted(tuples, key=lambda x: x[1])
# Example usage:
tuples = [(1, 3), (3, 2), (2, 1)]
print(sort_by_second_element(tuples)) # Output: [(2, 1), (3,
2), (1, 3)]
5. Lists of Dictionaries
o Question: Write a Python function to sort a list of dictionaries by the value of
a specific key.
python
def sort_by_key(list_of_dicts, key):
return sorted(list_of_dicts, key=lambda x: x[key])
# Example usage:
list_of_dicts = [{'name': 'Alice', 'age': 25}, {'name': 'Bob',
'age': 20}, {'name': 'Charlie', 'age': 23}]
print(sort_by_key(list_of_dicts, 'age'))
# Output: [{'name': 'Bob', 'age': 20}, {'name': 'Charlie',
'age': 23}, {'name': 'Alice', 'age': 25}]
Loops
1. For Loop
o Question: Write a Python function to print the first n Fibonacci numbers using
a for loop.
python
def fibonacci(n):
a, b = 0, 1
for _ in range(n):
print(a, end=' ')
a, b = b, a + b
# Example usage:
fibonacci(10) # Output: 0 1 1 2 3 5 8 13 21 34
2. While Loop
o Question: Write a Python function to find the factorial of a number using a
while loop.
python
def factorial(n):
result = 1
while n > 1:
result *= n
n -= 1
return result
# Example usage:
print(factorial(5)) # Output: 120
3. Nested Loops
o Question: Write a Python function to generate the multiplication table (from 1
to 10) for a given number.
python
def multiplication_table(n):
for i in range(1, 11):
print(f"{n} x {i} = {n * i}")
# Example usage:
multiplication_table(5)
# Output:
# 5 x 1 = 5
# 5 x 2 = 10
# 5 x 3 = 15
# 5 x 4 = 20
# 5 x 5 = 25
# 5 x 6 = 30
# 5 x 7 = 35
# 5 x 8 = 40
# 5 x 9 = 45
# 5 x 10 = 50
Functions
1. Function Definition
o Question: Write a Python function to check if a given number is prime.
python
def is_prime(n):
if n <= 1:
return False
for i in range(2, int(n ** 0.5) + 1):
if n % i == 0:
return False
return True
# Example usage:
print(is_prime(11)) # Output: True
print(is_prime(4)) # Output: False
2. Recursion
o Question: Write a Python function to compute the nth Fibonacci number using
recursion.
python
def fibonacci_recursive(n):
if n <= 0:
return 0
elif n == 1:
return 1
else:
return fibonacci_recursive(n - 1) +
fibonacci_recursive(n - 2)
# Example usage:
print(fibonacci_recursive(10)) # Output: 55
3. Lambda Functions
o Question: Write a Python lambda function to find the square of a number.
python
square = lambda x: x ** 2
# Example usage:
print(square(5)) # Output: 25
4. Higher-Order Functions
o Question: Write a Python function that takes a list and a function as arguments
and returns a new list with the function applied to each element.
python
def apply_function(lst, func):
return [func(x) for x in lst]
# Example usage:
nums = [1, 2, 3, 4, 5]
print(apply_function(nums, lambda x: x * 2)) # Output: [2, 4,
6, 8, 10]
5. Default Arguments
o Question: Write a Python function that prints a greeting message. If no name
is provided, it should greet "World" by default.
python
def greet(name="World"):
print(f"Hello, {name}!")
# Example usage:
greet() # Output: Hello, World!
greet("Alice") # Output: Hello, Alice!
Comprehensive Exercise
1. Data Structures and Functions
o Question: Write a Python program that reads a list of numbers from the user,
sorts the list, and prints the sorted list and the second highest number.
python
def get_user_input():
return list(map(int, input("Enter numbers separated by
spaces: ").split()))
def sort_numbers(numbers):
return sorted(numbers)
def second_highest(numbers):
sorted_numbers = sort_numbers(numbers)
return sorted_numbers[-2] if len(sorted_numbers) >= 2 else
None
# Example usage:
numbers = get_user_input()
sorted_numbers = sort_numbers(numbers)
print("Sorted numbers:", sorted_numbers)
print("Second highest number:", second_highest(numbers))