9 вопросов для собеседования по SQL в Apple

От анализа данных о продажах iPhone до управления музыкальным каталогом Apple Music – SQL используется в Apple довольно часто. Неудивительно, что именно поэтому Apple часто задает вопросы по работе с SQL на собеседованиях при приеме на работу в области анализа данных, науки о данных и инженерии данных. Итак, чтобы подготовиться к SQL-собеседованию, мы собрали 9 вопросов для SQL-собеседования Apple, которые похожи на вопросы, недавно заданные в Apple – сколько из них вы сможете решить?

В моем канале вы найдете много задач с собеседований и акутальных инструментов и гайдов для SQL разработчиков.

SQL Вопрос 1: Определите важных пользователей Apple

Компания Apple ведет базу данных своих клиентов, совершивших покупки в магазине App Store. По определению Apple, важный пользователь – это покупатель, совершивший не менее 10 покупок в месяц за последний год. Задача состоит в том, чтобы выявить важных для Apple пользователей App Store.

В таблице “Покупки” регистрируется каждая покупка. В качестве примера можно привести следующие данные:

purchases Example Input:

В таблице “Пользователи” хранятся данные о каждом пользователе. Пример данных:

users Example Input:

Запрос на SQL:

SELECT u.user_id, u.email
FROM users u
JOIN (
  SELECT p.user_id
  FROM purchases p
  WHERE p.purchase_date BETWEEN (CURRENT_DATE() - INTERVAL 1 YEAR) AND CURRENT_DATE()
  GROUP BY YEAR(p.purchase_date), MONTH(p.purchase_date), p.user_id
  HAVING COUNT(p.purchase_id) >= 10
) AS monthly_purchases
ON u.user_id = monthly_purchases.user_id
GROUP BY u.user_id
HAVING COUNT(monthly_purchases.user_id) = 12;

Этот SQL-запрос сначала группирует покупки по году, месяцу и идентификатору пользователя, обеспечивая подсчет покупок, совершенных в каждом месяце отдельно.

С помощью HAVING мы отфильтровываем пользователей, совершивших не менее 10 покупок в каждой группировке.

Затем этот список объединяется с таблицей “Пользователи”, чтобы получить данные о пользователях. Наконец, мы группируем данные по идентификатору пользователя и убеждаемся, что эти пользователи выполнили условие покупки за все 12 месяцев в прошлом году.

Чтобы решить похожий вопрос по анализу данных с помощью бесплатного интерактивного редактора SQL-кода DataLemur, попробуйте ответить на этот вопрос Microsoft Teams Power User SQL Interview Question.

SQL Вопрос 2: Анализ характеристик продукции Apple на основе отзывов

Учитывая таблицу отзывов, содержащую данные об отзывах о товарах, представленных пользователями с течением времени, напишите SQL-запрос для вычисления среднемесячного рейтинга каждого продукта Apple. Таблица отзывов имеет следующую схему:

• review_id: Целое число, уникально идентифицирующее каждый отзыв
• user_id: Целое число, идентифицирующее пользователя, который отправил отзыв
• submit_date: временное значение, отражающее время, когда был отправлен отзыв
• product_id: Целое число, идентифицирующее продукт, на который отправлен отзыв
• звезды: Целое число, указывающее количество звезд, полученных товаром в обзоре (от 1 до 5).

Обратите внимание, что для целей этой задачи можно предположить, что product_id соответствует продукту Apple, а дата отправки_даты для каждого отзыва имеет формат “MM/DD/YYY HH:MI:SS”.

reviews Example Input:

Пример вывода:

Ответ:

SELECT EXTRACT(MONTH FROM submit_date) as mth, 
       product_id as product, 
       AVG(stars) as avg_stars
FROM reviews
GROUP BY mth, product
ORDER BY mth, product;

Приведенный выше запрос извлекает месяц из submit_date с помощью функции EXTRACT и группирует по нему данные вместе с product_id. Затем используется агрегатная функция AVG для расчета среднего рейтинга, ежемесячно выставляемого пользователями каждому продукту Apple. Сортировка выполняется сначала по месяцу, а затем по идентификатору продукта.

Совет: Вопросы с агрегатными функциями, такими как AVG, очень полезны. Практикуйте больше таких вопросов на DataLemur.

SQL ВОПРОС 3: Что делают внешние ключи при проектировании баз данных?

Внешний ключ – это столбец или группа столбцов в таблице, которые ссылаются на первичный ключ в другой таблице. Ограничение внешнего ключа помогает поддерживать ссылочную целостность между двумя таблицами.

Давайте рассмотрим данные о сотрудниках из базы данных отдела кадров Apple:

apple_employees:

| employee_id| first_name | last_name | manager_id |

—-+ | 1 | Aubrey | Graham | 3 | | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter |

В этой таблице employee_id служит первичным ключом, а manager_id – внешним ключом, поскольку он связан с employee_id менеджера сотрудника. Таким образом, устанавливается связь между сотрудниками Apple и их менеджерами, что позволяет легко выполнять запросы, чтобы найти менеджера сотрудника или узнать, какие сотрудники подчиняются определенному менеджеру.

Таблица apple_employees также может иметь несколько внешних ключей, которые ссылаются на первичные ключи в других таблицах. Например, внешние ключи department_id и location_id могут использоваться для связи каждого сотрудника с его соответствующим отделом и местом работы.

Вопрос 4 SQL: Средние продажи продукции Apple

Как аналитик данных в компании Apple, вы должны понять, каковы показатели продаж различных продуктов Apple с течением времени, чтобы принимать обоснованные бизнес-решения. Вам даны две таблицы: продукты и продажи. В таблице products каждая строка представляет отдельный продукт, обозначенный product_id, продаваемый компанией Apple. Таблица продаж содержит данные о продажах этих продуктов, включая количествопродаж и датупродажи. Вычислите среднее количество каждого продукта, проданного за месяц в 2021 году.

Примеры таблиц,

products table:

sales table:

Ответ:

SELECT MONTH(s.date_of_sale) as 'Month', p.product_name, AVG(s.quantity_sold) as 'Average_Sold'
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE YEAR(s.date_of_sale) = 2021
GROUP BY Month, p.product_name

Этот запрос сначала выполняет внутреннее объединение продуктов и продаж по идентификатору product_id. Затем он группируется по месяцу и названию_продукта, чтобы вычислить среднее количество проданных товаров в каждом месяце для разных продуктов. Для получения данных только за 2021 год выполняется фильтрация по году.

ВОПРОС ПО SQL 5: Что такое хранимая процедура?

Хранимые процедуры похожи на функции в Python – они могут принимать входные параметры и возвращаемые значения и используются для инкапсуляции сложной логики.

Например, если бы вы работали аналитиком данных в команде маркетинговой аналитики Apple, обычной задачей могло бы быть нахождение коэффициента конверсии для ваших объявлений за определенный период времени. Вместо того чтобы писать этот запрос снова и снова, вы можете написать хранимую процедуру, как показано ниже:

CREATE FUNCTION get_conversion_rate(start_date DATE, end_date DATE, event_name TEXT)
RETURNS NUMERIC AS
$BODY$
BEGIN
  RETURN (SELECT COUNT(*) FROM events WHERE event_date BETWEEN start_date AND end_date AND event_name = 'conversion')
          / (SELECT COUNT(*) FROM events WHERE event_date BETWEEN start_date AND end_date AND event_name = 'impression');
END;
$BODY$
LANGUAGE 'plpgsql';

Чтобы вызвать эту хранимую процедуру, выполните следующий запрос:

SELECT get_conversion_rate('2023-01-01', '2023-01-31', 'conversion');

Вопрос 6 SQL: Рассчитайте коэффициент конверсии по клику для продуктов Apple

Предположим, вы работаете аналитиком данных в компании Apple. Ваша команда занимается повышением конверсии зрителей в покупателей для различных цифровых продуктов Apple. Вам предоставлены следующие две таблицы:

clicks Пример ввода:

cart_adds Пример ввода:

Ваша задача – написать SQL-запрос, который вычисляет коэффициент конверсии по клику, определяемый как количество пользователей, добавивших товар в корзину после клика на листинг продукта, деленное на общее количество кликов по продукту. Разбейте результат по product_id.

Ответ:

SELECT 
c.product_id, 
sum(case when a.add_id is not null then 1 else 0 end) / count(c.click_id) as conversion_rate
FROM 
clicks c
LEFT JOIN cart_adds a ON a.product_id = c.product_id AND a.user_id = c.user_id
GROUP BY c.product_id

SQL-запрос начинается с левого соединения таблицы clicks с таблицей cart_adds по идентификаторам product_id и user_id. Оператор case в функции sum подсчитывает количество записей, которые имеют совпадающие записи в таблице cart_adds (т. е. продукт был добавлен в корзину). Это число делится на общее количество кликов по каждому товару, чтобы получить коэффициент конверсии.

Чтобы решить похожий вопрос SQL-собеседования по показателю кликов с помощью бесплатного интерактивного редактора SQL-кода DataLemur, попробуйте ответить на этот вопрос Meta SQL-собеседования.

SQL Вопрос 7: Как ограничение FOREIGN KEY используется в базе данных?

FOREIGN KEY – это поле в таблице, которое ссылается на PRIMARY KEY другой таблицы. Оно создает связь между двумя таблицами и гарантирует, что данные в поле FOREIGN KEY являются достоверными.

Например, у вас есть данные аналитики продаж из CRM (инструмент управления взаимоотношениями с клиентами) компании Apple.

CREATE TABLE apple_accounts (
    account_id INTEGER PRIMARY KEY,
    account_name VARCHAR(255) NOT NULL,
    industry VARCHAR(255) NOT NULL
);

CREATE TABLE opportunities (
    opportunity_id INTEGER PRIMARY KEY,
    opportunity_name VARCHAR(255) NOT NULL,
    account_id INTEGER NOT NULL,
    FOREIGN KEY (account_id) REFERENCES apple_accounts(account_id)
);

Ограничение FOREIGN KEY гарантирует, что данные в поле account_id таблицы “Возможности” действительны, и предотвращает вставку строк в таблице “Возможности”, не имеющих соответствующих записей в таблице apple_accounts. Оно также помогает усилить связь между двумя таблицами и может быть использовано для обеспечения того, чтобы данные не удалялись из таблицы счетов, если на них все еще есть ссылки в таблице возможностей.

SQL Вопрос 8: Найти максимальное количество проданных продуктов Apple

Apple продает множество продуктов, таких как iPhone, iPad, Macbook и т.д. Каждый продукт имеет свою модель и год выпуска. Задача состоит в том, чтобы выяснить максимальное количество проданных единиц каждого продукта Apple в любом году, то есть год и модель продукта, который был продан больше всего.

Вот таблицы:

sales Пример ввода:

Пример вывода:

Ответ:

SELECT product_model, product_year, SUM(quantity) as max_quantity 
FROM sales 
GROUP BY product_model, product_year 
ORDER BY max_quantity DESC;

Этот запрос сегментирует таблицу продаж по кортежам (product_model, product_year) и суммирует количество проданных товаров для каждого сегмента. Затем он сортирует результаты в порядке убывания общего количества проданных товаров, то есть модели товаров с наибольшим количеством проданных товаров окажутся вверху.

Вопрос 9 SQL: Рассчитайте общий объем продаж одного продукта, произведенного компанией Apple

Вам предоставлен доступ к двум базам данных. Одна содержит информацию о клиентах Apple, например их идентификатор, имя и местоположение, а другая – информацию о товарах, например, идентификатор, имя и цену товара. Одна из наиболее часто выполняемых задач – вычисление общего объема продаж определенного продукта. Напишите SQL-запрос, который вычислит и вернет список продуктов, проданных Apple Inc, вместе с их общим объемом продаж.

DisplayName содержит название продукта, продаваемого Apple Inc, unit price – цену за единицу, а quantity – количество единиц, проданных клиентам.

Customer Пример ввода:

Product Пример ввода:

Ответ:

SELECT 
    P.DisplayName AS Product,
    SUM(P.UnitPrice) AS TotalSales
FROM 
    Product P
JOIN 
    Customer C 
ON 
    P.CustomerId = C.CustomerId
GROUP BY 
    P.DisplayName

Это даст вам список всех продуктов вместе с их общими продажами. Для каждого продукта мы суммируем его цену за каждый раз, когда он был куплен, объединив таблицы Product и Customer по их общему столбцу CustomerId и сгруппировав результат по названию продукта.

Поскольку на собеседованиях по SQL часто встречаются вопросы о соединениях, попробуйте ответить на этот интерактивный вопрос Snapchat Join SQL.

Подготовка к собеседованию по SQL в Apple

Лучший способ подготовиться к SQL-собеседованию в Apple, помимо того, что вы должны быть уверены, что владеете основами SQL, – это отработать тонну реальных вопросов по SQL, которые задавались на последних собеседованиях в Apple.