Questions asked in Data Scientist Interviews Part 1

Online Streaming Service Traffic Report (SQL)

Create a query for an online streaming service. It should return a list of clients, their number of streams, and the total amount of traffic The result should have the following columns: mac_address / streams / totaltraffic. mac address – client MAC address streams – total number of streams for a specific client total traffic – total traffic amount of all streams for a specific client The resuit should be sorted in descending order by total trafi.

Note: streams of “720p” quality or higher should be included in the result

SELECT
    c.mac_address,
    COUNT(s.stream_id) AS streams,
    SUM(s.traffic_amount) AS totaltraffic
FROM
    client c
JOIN
    streaming s ON c.client_id = s.client_id
WHERE
    s.quality >= '720'
GROUP BY
    c.mac_address
ORDER BY
    totaltraffic DESC;

In the above query:

• We join the client table (aliased as c) with the streaming table (aliased as s) using the client_id as the common key.
• We select the mac_address from the client table.
• We count the streams using COUNT(s.stream_id) and sum the traffic using SUM(s.traffic_amount) for each client.
• We filter streams with a quality of “720p” or higher using the WHERE clause.
• We group the results by mac_address to aggregate the data for each client.
• Finally, we sort the result in descending order by totaltraffic.

This query will provide you with the list of clients, their number of streams, and the total amount of traffic, as requested.

Natural Language Processing:

Stop words are commonly used words like “a”, “is”, and “the”. They are typically filtered out during processing ALL Implement a function that takes a string text and an integer k, and returns the list of words that occur in the text at least ktimes. The words must be returned in the order of their first occurrence in the text.

def stop_words(text, k):
    words = text.split()
    word_counts = {}
    result = []

    for word in words:
        # Check if the word is already in the dictionary, and if not, add it
        if word not in word_counts:
            word_counts[word] = 0
        word_counts[word] += 1

        # If the word has occurred k times, add it to the result list
    for word in words:    
        if word_counts[word] == k:
            result.append(word)

    return result

# Example usage:
text = "a mouse is smaller than a dog but a dog is stronger"
k = 2
result = stop_words(text, k)
print(result)  # Output: ["a", "is", "dog"]

The above code defines a Python function stop_words(text, k) that takes a string text and an integer k as input and returns a list of words that occur in the text at least k times. The words in the result list are in the order of their first occurrence in the text.

Check out Questions asked in Data Scientist Interviews Part 3 and Part 2

Here’s a step-by-step explanation of the code:

1. The function stop_words(text, k) begins by splitting the input text into individual words using the split() method. This splits the text into a list of words based on spaces.
2. It initializes an empty dictionary called word_counts to keep track of the word frequencies in the text. The keys of this dictionary are words, and the values are the number of times each word has been encountered in the text.
3. An empty list called result is created to store the words that occur at least k times.
4. The code then enters a loop that iterates through each word in the list of words obtained from splitting the input text.
5. For each word, it checks if the word is already present in the word_counts dictionary. If the word is not present, it adds the word to the dictionary and sets its count to 0. If the word is already in the dictionary, it increments its count by 1.
6. After counting the occurrences of all words in the text, the code enters a second loop that iterates through the words in the original list again.
7. In the second loop, it checks if the word’s count in the word_counts dictionary is equal to k. If it is, the word is appended to the result list.
8. Finally, the function returns the result list, which contains the words that occurred at least k times in the input text.

In the example usage provided, the input text is “a mouse is smaller than a dog but a dog is stronger,” and k is set to 2. The function returns the list [“a”, “is”, “dog”], as these words occurred at least 2 times in the text, and their order of appearance is preserved.