Data Engineering Questions:

1. What are the challenges with implementing Data fusion with Dataproc?
2. Key components and best practice to build scalable data pipeline?
3. Explain what is data mart and how it differ from data warehouse?
4. What strategies can organization apply for fault tolerance with DataFusion?
5. What are the benefits and challenges in cloud compared to on premises?

SQL Task 1

# Database Schema

## `employees`
| Column      | Data Type  |
|------------|-----------|
| id         | int       |
| first_name | varchar   |
| last_name  | varchar   |
| salary     | int       |
| manager_id | int       |

## `employees_projects`
| Column       | Data Type  |
|-------------|-----------|
| employee_id | int       |
| project_id  | int       |
| assigned_date | date     |

## `projects`
| Column      | Data Type  |
|------------|-----------|
| id         | int       |
| title      | varchar   |
| start_date | date      |
| budget     | int       |

Task:

Retrieve all managers and show the average salary of their employees.

• The manager/employee hierarchy is only one level deep.
• A manager is any employee with NULL in manager_id.
• If a manager has no employees, the average salary should be 0.

Expected Output Format | id | first_name | last_name | average_employee_salary |

SQL Task 2

Explain what will be in the output of the following query?

SELECT mgr.id,
       mgr.first_name,
       mgr.last_name,
       prj.id AS project_id,
       prj.title AS project_title,
       prj.start_date
FROM employees AS mgr
LEFT JOIN employees_projects AS ep ON ep.employee_id = mgr.id
LEFT JOIN projects AS prj ON ep.project_id = prj.id
WHERE mgr.manager_id IS NULL AND
      prj.id = (
          SELECT prj2.id AS project_id
          FROM employees AS mgr2
          JOIN employees_projects AS ep2 ON ep2.employee_id = mgr2.id
          JOIN projects AS prj2 ON ep2.project_id = prj2.id
          WHERE mgr2.id = mgr.id
          ORDER BY prj2.start_date DESC
          LIMIT 1
      );

Python Task 1

For this question, you are given a log file from a simple web server. 
Each line in the log file contains a URL and nothing else. 
Your job is to write code that will fetch/download the log file 
from the internet, process it, and output the most popular URL in the file. 
You do not need to normalize the URLs in the log file.

Use the following sample log files:

- logFile1 = "<https://public.karat.io/content/urls2.txt>" 
-> the most common URL is <http://www.example.com> (with 1170 occurrences)
- logFile2 = "<https://public.karat.io/content/q015/single_url.txt>" 
-> the result is <http://www.example.com> (1 occurrence)

For example, if the log file contains the following lines:

<http://www.example.com/living> 
<http://www.example.com/2014/07/08/worldsport/gallery/what-a-shot-0708/index.html> 
<http://www.example.com/2014/07/04/health/mosquito-bites-myths/index.html> 
<http://www.example.com/living> <https://www.example.com/living> 
<http://www.example.com/trends> <http://www.example.com/profile>

The expected output is `"<http://www.example.com/living> (2 occurrences)"

Python Task 2