The questions in this file are taken from the following link: Visit DataLemur Questions

Assume you're given a table Twitter tweet data, write a query to obtain a histogram of tweets posted per user in 2022. Output the tweet count per user as the bucket and the number of Twitter users who fall into that bucket.

In other words, group the users by the number of tweets they posted in 2022 and count the number of users in each group.

Assuming you have a table named tweets with the following columns:

SELECT
    tweet_bucket,
    COUNT(*) AS users_num
FROM (
    SELECT
        COUNT(*) AS tweet_bucket
    FROM
        tweets t
    WHERE
        EXTRACT(YEAR FROM t.tweet_date) = '2022'
    GROUP BY
        t.user_id
    ORDER BY
        COUNT(*)
) AS user_tweet_counts
GROUP BY
    user_tweet_counts.tweet_bucket;

Given a table of candidates and their skills, the task is to identify candidates best suited for an open Data Science job. The goal is to find candidates proficient in Python, Tableau, and PostgreSQL.

Write a query to list the candidates who possess all of the required skills for the job. Sort the output by candidate ID in ascending order.

There are no duplicates in the candidates table.

SELECT
    candidate_id
FROM
    candidates
WHERE
    skill IN ('Python', 'Tableau', 'PostgreSQL')
GROUP BY
    candidate_id
HAVING
    COUNT(skill) = 3
ORDER BY
    candidate_id;

Assume you're given two tables containing data about Facebook Pages and their respective likes (as in "Like a Facebook Page").

Write a query to return the IDs of the Facebook pages that have zero likes. The output should be sorted in ascending order based on the page IDs.

SELECT
    p.page_id
FROM
    pages p
LEFT JOIN
    page_likes pl ON p.page_id = pl.page_id
WHERE
    pl.page_id IS NULL
ORDER BY
    p.page_id;

Tesla is currently investigating production bottlenecks and needs your assistance to extract relevant data. The task is to write a query that determines which parts have begun the assembly process but are not yet finished.

• The parts_assembly table contains all parts currently in production, each at varying stages of the assembly process.
• An unfinished part is one that lacks a finish_date.

This question is straightforward, so let's approach it with simplicity in both thinking and solution.

SELECT
    pa.part,
    pa.assembly_step
FROM
    parts_assembly pa
WHERE
    pa.finish_date IS NULL;

Assume you're given a table on user viewership categorized by device type, where the three types are laptop, tablet, and phone.

Write a query that calculates the total viewership for laptops and mobile devices, where mobile is defined as the sum of tablet and phone viewership. Output the total viewership for laptops as laptop_views and the total viewership for mobile devices as mobile_views.

WITH laptop_views AS (
    SELECT COUNT(*) AS laptop_views
    FROM viewership
    WHERE device_type = 'laptop'
), mobile_views AS (
    SELECT COUNT(*) AS mobile_views
    FROM viewership
    WHERE device_type != 'laptop'
)
SELECT
    laptop_views,
    mobile_views
FROM
    laptop_views, mobile_views;

SELECT 
  SUM(CASE WHEN device_type = 'laptop' THEN 1 ELSE 0 END) AS laptop_views, 
  SUM(CASE WHEN device_type IN ('tablet', 'phone') THEN 1 ELSE 0 END) AS mobile_views 
FROM viewership;

Given a table of Facebook posts, the task is to, for each user who posted at least twice in 2021, write a query to find the number of days between each user’s first post of the year and last post of the year in 2021. Output the user and the number of days between each user's first and last post.

SELECT
    user_id,
    DATE_PART('day', MAX(post_date) - MIN(post_date)) AS days_between
FROM
    posts
WHERE
    DATE_PART('year', post_date) = 2021
GROUP BY
    user_id
HAVING
    COUNT(user_id) > 1;

Write a query to identify the top 2 Power Users who sent the highest number of messages on Microsoft Teams in August 2022. Display the IDs of these 2 users along with the total number of messages they sent. Output the results in descending order based on the count of the messages.

No two users have sent the same number of messages in August 2022.

SELECT
    sender_id,
    COUNT(*) AS message_count
FROM
    messages
WHERE
    DATE_PART('month', sent_date) = '08' AND DATE_PART('year', sent_date) = '2022'
GROUP BY
    sender_id
ORDER BY
    message_count DESC
LIMIT
    2;

Given a number ( n ), write a formula that returns ( n! ), the factorial of ( n ).

In case you forgot the factorial formula:

$n! = n \times (n-1) \times (n-2) \times \ldots \times 2 \times 1 $

For example, $( 5! = 5 \times 4 \times 3 \times 2 \times 1 = 120 )$, so we'd return 120.

Assume ( n ) is a non-negative integer.

def factorial(n):
    if n < 1:
        return 1
    else:
        return n * factorial(n - 1)

Write a function fizz_buzz_sum to find the sum of all multiples of 3 or 5 below a target value.

For example, if the target value was 10, the multiples of 3 or 5 below 10 are 3, 5, 6, and 9.

Because $(3 + 5 + 6 + 9 = 23)$, our function would return 23.

def fizz_buzz_sum(target):
  sum = 0
  for i in range(target):
    if (i % 3 == 0 or i % 5 == 0):
      sum += i
  return sum

Write a function to get the intersection of two lists.

For example, if $(A = [1, 2, 3, 4, 5])$, and $(B = [0, 1, 3, 7])$, then you should return $([1, 3])$.

def intersection(a, b):
    result = []
    for i in a:
        if i in b:
            result.append(i)
    return result

def intersection(a, b):
    return list(set(a) & set(b))

Given an input list containing $(n)$ distinct numbers in the range 0 to $(n)$, return the only number in the range that is missing from the list.

For example, given input = [0, 1, 3], return 2. Because the input list has 3 elements, we expect to see the numbers 0 to 3 in there, but 2 is missing.

Another example: given input = [4, 3, 2, 1], return 0. We return 0 because the input list has 4 elements, so we expect to see the numbers 0 to 4 in there, but 0 itself is missing!

def missing_int(input: list[int])-> int:
  for i in range(len(input)):
    if i in input:
      continue
    return i
  return len(input)

Assume you're given a table containing job postings from various companies on the LinkedIn platform. Write a query to retrieve the count of companies that have posted duplicate job listings.

Duplicate job listings are defined as two job listings within the same company that share identical titles and descriptions.

SELECT
    COUNT(*) AS duplicate_companies
FROM (
    SELECT
        COUNT(*) AS duplicate_companies
    FROM
        job_listings
    GROUP BY
        company_id, title, description
    HAVING
        COUNT(DISTINCT job_id) > 1
) AS dc;

Assume you're given the tables containing completed trade orders and user details in a Robinhood trading system.

Write a query to retrieve the top three cities that have the highest number of completed trade orders listed in descending order. Output the city name and the corresponding number of completed trade orders.

SELECT
    u.city,
    COUNT(*) AS total_orders
FROM
    trades t
JOIN
    users u ON t.user_id = u.user_id
WHERE
    t.status = 'Completed'
GROUP BY
    u.city
ORDER BY
    total_orders DESC
LIMIT
    3;

Given a list of integers called input, return True if any value appears at least twice in the array. Return False if every element in the input list is distinct.

For example, if the input list was [1, 3, 5, 7, 1], then return True because the number 1 shows up twice.

However, if the input was [1, 3, 5, 7], then return False, because every element of the list is distinct.

def contains_duplicate(input)-> bool:
  l = set()
  for i in input:
    if i in l:
      return True
    else:
      l.add(i)
  return False

Given a list of salaries, we'll define a metric called inequity, which is the difference between the maximum and minimum salary seen in the list:

inequity = max(input_list) - min(input_list)

Write a function called min_inequity that takes in a list of salaries and a value $( n )$, and returns the minimum inequity possible when taking $( n )$ salaries from the full salary list.

If that was hard to understand, you're not alone – let's break it down with some examples.

salaries = [60000, 80000, 120000, 70000]

n = 2

The minimum inequity is $10,000, because max(60000, 70000) - min(60000, 70000) = 10000.

salaries = [60000, 80000, 120000, 70000]

n = 3

The minimum inequity is $20,000, because max(60000, 70000, 80000) - min(60000, 70000, 80000) = 20000.

def min_inequity(salaries, n):
	salaries.sort()
	return salaries[n-1] - salaries[0]

You are given a list of stock prices, where the stock's price on the (i^{th}) day is stored as the (i^{th}) element of the prices list.

You want to maximize your profit trading the stock, but are only allowed to buy the stock once and sell it once on a future day.

Write a function called max_profit which takes in this list of stock prices, and computes the max profit possible. Return 0 if you can't make any profit.

Input: prices = [9, 1, 3, 6, 4, 8, 3, 5, 5]

Output: 7 Explanation: Buy on day 2 (price = 1) and sell on day 6 (price = 8), profit = 8 - 1 = 7.

Note that buying on day 2 and selling on day 1 is not allowed because you have to buy the stock BEFORE you can sell it (unless you're a time-traveller, in which case just trade bitcoin).

Input: prices = [6, 4, 3, 3, 2]

Output: 0 Explanation: In this case, no trades should be made since the stock is tanking like a brick. The max profit here is 0.

def max_profit(prices):
    buy = prices[0]
    profit = 0
    for i in prices:
        if i - buy > profit:
            profit = i - buy
        if i < buy:
            buy = i
    return profit

Given the reviews table, write a query to retrieve the average star rating for each product, grouped by month. The output should display the month as a numerical value, product ID, and average star rating rounded to two decimal places. Sort the output first by month and then by product ID.

SELECT
    EXTRACT("month" FROM submit_date) AS mth,
    product_id AS product,
    AVG(stars)::numeric(10,2) AS avg_stars
FROM
    reviews
GROUP BY
    mth, product
ORDER BY
    mth, product;

Assume you have an events table on Facebook app analytics. Write a query to calculate the click-through rate (CTR) for the app in 2022 and round the results to 2 decimal places.

Definition and note:

• Percentage of click-through rate (CTR) = 100.0 * Number of clicks / Number of impressions
• To avoid integer division, multiply the CTR by 100.0, not 100.

SELECT 
    app_id, 
    (100.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) / 
    SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END))::numeric(10,2) AS ctr
FROM 
    events
WHERE 
    EXTRACT(YEAR FROM timestamp) = '2022'
GROUP BY 
    app_id;

Assume you're given tables with information about TikTok user sign-ups and confirmations through email and text. New users on TikTok sign up using their email addresses, and upon sign-up, each user receives a text message confirmation to activate their account.

Write a query to display the user IDs of those who did not confirm their sign-up on the first day, but confirmed on the second day.

Definition:

action_date refers to the date when users activated their accounts and confirmed their sign-up through text messages.

SELECT 
  DISTINCT user_id 
FROM 
  emails e
JOIN 
  texts t 
ON 
  e.email_id = t.email_id
WHERE 
  t.signup_action = 'Confirmed'
AND 
  DATE_PART('day', t.action_date::timestamp - e.signup_date::timestamp) = 1;

Your team at JPMorgan Chase is preparing to launch a new credit card, and to gain some insights, you're analyzing how many credit cards were issued each month.

Write a query that outputs the name of each credit card and the difference in the number of issued cards between the month with the highest issuance cards and the lowest issuance. Arrange the results based on the largest disparity.

SELECT card_name,
       (MAX(issued_amount) - MIN(issued_amount)) AS difference
FROM 
  monthly_cards_issued
GROUP BY 
  card_name
ORDER BY 
  difference
DESC;

You're trying to find the mean number of items per order on Alibaba, rounded to 1 decimal place using tables which includes information on the count of items in each order (item_count table) and the corresponding number of orders for each item count (order_occurrences table).

There are a total of 500 orders with one item per order, 1000 orders with two items per order, and 800 orders with three items per order.

SELECT 
  ROUND(
    SUM(item_count::DECIMAL*order_occurrences) / SUM(order_occurrences), 1
  ) AS mean
FROM 
  items_per_order;

CVS Health is trying to better understand its pharmacy sales, and how well different products are selling. Each drug can only be produced by one manufacturer.

Write a query to find the top 3 most profitable drugs sold, and how much profit they made. Assume that there are no ties in the profits. Display the result from the highest to the lowest total profit.

Definition:

• cogs stands for Cost of Goods Sold which is the direct cost associated with producing the drug.
• Total Profit = Total Sales - Cost of Goods Sold

SELECT 
  drug, 
  total_sales - cogs AS total_profit
FROM 
  pharmacy_sales
ORDER BY 
  total_profit DESC
LIMIT 
  3;

CVS Health is analyzing its pharmacy sales data, and how well different products are selling in the market. Each drug is exclusively manufactured by a single manufacturer.

Write a query to identify the manufacturers associated with the drugs that resulted in losses for CVS Health and calculate the total amount of losses incurred.

Output the manufacturer's name, the number of drugs associated with losses, and the total losses in absolute value. Display the results sorted in descending order with the highest losses displayed at the top.

SELECT 
    manufacturer, 
    COUNT(drug) AS drug_count, 
    SUM(cogs - total_sales) AS total_loss
FROM 
    pharmacy_sales
WHERE 
    cogs - total_sales > 0
GROUP BY 
    manufacturer
ORDER BY 
    total_loss DESC;

CVS Health wants to gain a clearer understanding of its pharmacy sales and the performance of various products.

Write a query to calculate the total drug sales for each manufacturer. Round the answer to the nearest million and report your results in descending order of total sales. In case of any duplicates, sort them alphabetically by the manufacturer name.

Since this data will be displayed on a dashboard viewed by business stakeholders, please format your results as follows: "$36 million".

SELECT 
    manufacturer,
    CONCAT('$', ROUND(SUM(total_sales) / 1000000, 0), ' million') AS sales
FROM 
    pharmacy_sales
GROUP BY 
    manufacturer
ORDER BY 
    SUM(total_sales) DESC,
    manufacturer;

Assume you are given the table below on Uber transactions made by users. Write a query to obtain the third transaction of every user. Output the user id, spend and transaction date.

WITH numbered_transactions AS (
    SELECT 
        user_id,
        spend,
        transaction_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS transaction_rank
    FROM 
        transactions
)
SELECT 
    user_id,
    spend,
    transaction_date
FROM 
    numbered_transactions
WHERE 
    transaction_rank = 3;