# Python - 30mins

## Task 1: Parse an encoded string
In this Python challenge, you need to write a function that accepts an encoded string as a parameter. This string will contain a first name, last name, and an id.

Values in the string can be separated by any number of zeros. The id is a numeric value but will contain no zeros. The function should parse the string and return a Python dictionary that contains the first name, last name, and id values.

An example input would be:

"Robert000Smith000123".

The function should return the following output:

{ "first_name": "Robert", "last_name": "Smith", "id": "123" }

## Task 2
[Given a string which could contains numbers and characters. Please write a program using list comprehension to provide frequency of characters with an output of sorted dictionary](https://codeshare.io/yoPoJn)

Example for 2:
# "aaddbsbbbdcc" -> {'a':2, 'b': 4, 'c':2, 'd':3}
# "2234413" -> {'1':1, '2':2, '3':2, '4':2}

## Task 3
[Given a list of names and a letter, returns the names that start with that letter](https://codeshare.io/yoPoJn)

Example for 3:
# "n", ["Nicola", "christoph", "akos", "pablo", "nabila"] -> ["nicola", nabila"]
# 1, ["nicola", "christoph", "akos", "pablo", "nabila"] -> []
# "b", ["nicola", "christoph", "akos", "pablo", "nabila", 1234, list()] -> []

## Task 4
[Given a string which is an english sentence. Please write a python program(function) to check if the string is a pangram. A pangram is a sentence containing every letter in the english alphabet(a to z). If it is a pangram then print "yes", otherwise return how many letters it is short of becoming a pangram.](https://codeshare.io/yoPoJn)

Example for 4:
# input = "The Quick Brown Fox Jumps Over The Lazy Dog" -> "yes"
# input = "The Quick Brown Fox Jumps Over The Lazy Cat" -> 2

## Questions
1. What is a decorator?
2. What is the difference between tuples and list?
3. Where do you use `yield`?
4. How can you do type annotation in Python?

# SQL
## Understanding a query - 20mins
1. What does this query try to do?
2. Does it run? And how would you optimize it?

WITH recent_articles 
AS (
  SELECT
    target_market,
    article_id,
    published_at,
    RANK() OVER (PARTITION BY target_market ORDER BY published_at) r
  FROM articles   
)
SELECT
  target_market,
  article_id,
  published_at,
  SUM(clicks) AS total_clicks
FROM recent_articles s
  JOIN article_interactions i
    ON i.article_id = s.article_id
WHERE published_at > '2021-01-01' AND r > 5
GROUP BY 1, 2
HAVING total_clicks > 3;

Notes:
- `article_id` is unique only within a target market
- `articles` table contains data since 2019
- `article_interactions` table includes the following columns: `target_market`, `article_id`,`clicks`, `interacted_at`

## Write an SQL query to find all participants that appear at least three times consecutively.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| year        | int     |
+-------------+---------+

id is the primary key for this table and id is an autoincrement column.

participants
╔════╦══════╦══════╗
║ id ║ name ║ year ║
╠════╬══════╬══════╣
║  1 ║ Arun ║ 2001 ║
║  2 ║ Arun ║ 2002 ║
║  3 ║ Arun ║ 2003 ║
║  4 ║ Arun ║ 2004 ║
║  5 ║ John ║ 2009 ║
║  6 ║ John ║ 2010 ║
║  7 ║ John ║ 2011 ║
║  8 ║ Bala ║ 2014 ║
║  9 ║ Bala ║ 2015 ║
║ 10 ║ Bala ║ 2017 ║
║ 11 ║ chan ║ 2014 ║
║ 12 ║ chan ║ 2015 ║
║ 13 ║ chan ║ 2017 ║
╚════╩══════╩══════╝

Output should be like this:

╔══════╗
║ name ║
╠══════╣
║ Arun ║
║ John ║
╚══════╝

Return the resultant table in any order.

## Theoretical questions
1. What is the difference between WHERE and HAVING?
2. What is the difference between UNION and UNION ALL?
3. What is the difference between FULL OUTER JOIN and CROSS JOIN (aka Cartesian JOIN)?
4. What is the difference between DELETE and TRUNCATE?
5. What are window functions?
6. What is the difference between RANK(), DENSE_RANK() and ROW_NUMBER() ?
7. What does ACID stand for? Why is it important?

# Design & Architecture
The BI and data science team extracts the data from snowflake datawarehouse to perform trends analysis and reporting. We are ingesting the data from multiple sources like APIs, events and legacy databases.
1. How will you design a framework to ingest the data from multiple sources to create a datalake and datawarehouse to help the BI team for reporting and dashboarding? Please feel free to use [excalidraw](https://excalidraw.com) to draw.
2. How will you apply data security in your framework?
3. How will you move data from legacy database(mysql) to AWS?
4. How will you apply monitoring and alerting/notifications in your system at various stages?

### (Optional) Oracle
1. What is and PCTFree index?
2. What is a bitmap index and its use cases?

# Distributed data processing -15mins

## General
1. What is skew? Why is it good/bad for data processing/storage?
2. Explain columnar data storage. What are the advantages/disadvantages of it?

## Snowflake
1. What is Snowpipe?
2. What are Stages?
3. What are warehouses?
4. How does Snowflake physically store the data?
5. Which types of indexes does Snowflake offer?
6. What is the difference between `CREATE TABLE ... LIKE/CLONE ...`?
7. How do you ensure referential integrity in Snowflake?

## (Optional) Redshift
1. What is distkey and sortkey?
2. What are the distribution styles and use cases for them?
3. How does Redshift leverage columnar orientation?
4. What type of indexes does Redshift offer?

## (Optional) Kafka
1. What are topics and partitions?
2. What happens inside Kafka when a producer is sending data to it?
3. What are consumer groups?

# Docker - 10mins
What is the difference between:
- ENTRYPOINT and CMD?
- ADD and COPY?
- `docker exec` and `docker run`?

# AWS - 15mins
1. What does this error tell you? How would you resolve it?

An error occurred (AccessDenied) when calling the ListObjects operation: Access Denied

2. Can I have a bucket called `my-bucket` and Akos will create the same `my-bucket` bucket in this own AWS account one day later?

3. You are trying to upload a 6GB file to AWS S3 and receive the message saying that "Your proposed upload exceeds the maximum allowed object size". What is a possible solution for the problem?

5. We have multiple groups in a company e.g IT, HR, Directors, Developers etc and we want to give access to specific S3 files to one or more than one group. What would be the cost effective way to do that?

6. Can we ensure that API calls to Amazon DynamoDB or S3 from Amazon EC2 instances in a VPC do not traverse the internet. if yes then how?

7. We have an application where user can update, delete and add new files into his S3 folder. What should we do to prevent the accidental deletion of the users data by him or anyone?

# Airflow - 15mins
1. How does the Airflow architecture look like?
2. How are DAGs scheduled?
3. Which of the following DAG definitions are not working?
a. DAG(dag_id=..., start_date=)
a. DAG( start_date=)
a. DAG(dag_id=..., default_args={start_date=...})
b. Task without DAG, task1 = Bash
c. Tasks with context manager
4. How do you test your DAGs?