Describe your understanding of SQL basic concepts such as Primary/Foreign Key, Joins, Union/Union All. Write SQL queries involving JOINs across 2-3 tables, Aggregation, Subqueries, Window functions, and CTEs (Leetcode- Easy and Medium). Explain statistical concepts and business metrics you have used in previous projects, KPIs you have built for analyzing data, strategies and tools to compare products, and how you would approach situation-based questions to improve efficiency and reduce query run time.
Please sign-in to view the solution
Explain the differences between linear regression and logistic regression. What are R-squared and P-value? Provide an example SQL query.
Please sign-in to view the solution
Write a SQL query to find the top 5 customers based on total expenditure over the last year.
Additionally, create a Python script to plot a bar chart showing total monthly orders over the same period.
Sample Input Data
orders
Table:
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1 | 1 | 2022-11-20 | 150.75 |
| 2 | 2 | 2022-12-05 | 200.50 |
| 3 | 1 | 2022-12-20 | 75.00 |
| 4 | 3 | 2023-01-05 | 300.00 |
| 5 | 2 | 2023-01-15 | 180.75 |
| 6 | 1 | 2023-02-10 | 120.00 |
| 7 | 4 | 2023-02-20 | 250.00 |
| 8 | 3 | 2023-03-01 | 90.00 |
customers
Table:
| customer_id | customer_name | signup_date |
|-------------|---------------|-------------|
| 1 | Alice | 2021-05-01 |
| 2 | Bob | 2022-03-15 |
| 3 | Carol | 2022-07-22 |
| 4 | Dave | 2022-09-19 |
Please sign-in to view the solution
Write a SQL query to find the top 3 products based on total sales value.
Additionally, create a Python script to plot a line graph showing daily total sales over the past month.
Lastly, explain how you would use Excel to calculate and visualize the sales trend.
Input:
transactions
Table:
| transaction_id | date | product_id | quantity | price |
|----------------|------------|------------|----------|--------|
| 1 | 2023-09-01 | 101 | 2 | 10.00 |
| 2 | 2023-09-01 | 102 | 1 | 20.00 |
| 3 | 2023-09-02 | 101 | 1 | 10.00 |
| 4 | 2023-09-02 | 103 | 3 | 15.00 |
| 5 | 2023-09-03 | 102 | 2 | 20.00 |
| 6 | 2023-09-03 | 101 | 2 | 10.00 |
Please sign-in to view the solution
When is it appropriate to use mean versus median in data analysis? Provide specific examples where one measure may be preferred over the other.
Please sign-in to view the solution
How do you write a query to calculate the Root Mean Square Error (RMSE) in SQL? Assume you have two columns, Actual
and Predicted
, in a table called Predictions
.
Please sign-in to view the solution
In a panel interview, you're given two mathematical problems. The first involves solving an algebra equation, and the second involves calculating speed, distance, or time given two of these variables. Provide solutions for the following problems:
- Solve the algebraic equation: 3x + 5 = 2x - 7
- If a car travels 150 kilometers in 3 hours, what is its average speed in kilometers per hour?
Please sign-in to view the solution
You are given a dataset sales_data
with a column sales_amount
representing the sales figures for different transactions. Write Python code to calculate the mean, median, and standard deviation of the sales amounts.
Please sign-in to view the solution
An IT company has a helpdesk that supports two types of issues: software and hardware. Based on past records, 60% of the issues are software-related, and 40% are hardware-related. The probability that a software issue is resolved within an hour is 70%, and the probability that a hardware issue is resolved within an hour is 50%. If an issue is resolved within an hour, what is the probability that it was a software issue?
Please sign-in to view the solution
You need to write an SQL query that identifies whether a given integer in a table is a prime number. Assume the table numbers
has a single column value
containing positive integers. Write a query to determine if each number is prime and return the value
along with a is_prime
flag. Provide the steps and the final SQL query.
Please sign-in to view the solution