logo

9 Adobe SQL Interview Questions (Updated 2024)

Updated on

December 25, 2023

At Adobe, SQL is used all the damn time for analyzing customer usage patterns for it's design products, and for managing large-scale customer data as part of the Adobe Experience Platform.

Because of this, Adobe almost always evaluates jobseekers on SQL query questions during interviews for Data Analytics, Data Science, and Data Engineering jobs. That's why we've collected 9 Adobe SQL interview questions to practice, which are similar to recently asked questions at Adobe – can you solve them?

9 Adobe SQL Interview Questions

SQL Question 1: Calculate Monthly Average Rating for Adobe Products

Let's assume that Adobe has an application marketplace where various applications are sold. You have given a table named "reviews" which contains user reviews for different applications. Each review record consists of review_id (unique identifier for each review), user_id (unique identifier for the user), submit_date (the date when review was submitted in MM/DD/YYYY HH24:MI:SS format), product_id (unique identifier for each product) and stars (rating given by the user to the product). Write an SQL query to calculate the monthly average rating (stars) for each product. Please round off the average rating up to two decimal places.

Example Input:
review_iduser_idsubmit_dateproduct_idstars
6171123'2022-06-08'500014
7802265'2022-06-10'698524
5293362'2022-06-18'500013
6352192'2022-07-26'698523
4517981'2022-07-05'698522
Example Output:
MonthProductAvg_Stars
6500013.50
6698524.00
7698522.50

Answer:


This SQL query first extracts the month from submit_date using EXTRACT function. Then it groups the data by Month and product_id. For each group, it calculates the average of stars. The result is sorted by Month and Product. The ROUND function is used to round off the average rating up to two decimal places.

To practice a similar window function interview problem which uses RANK() on DataLemur's free interactive coding environment, solve this Amazon SQL Interview Question: Amazon SQL Interview Question

SQL Question 2: Adobe User Behavior

Adobe has an extensive database of its customers. They often want to focus on active users who are highly engaged with their products. For this question, suppose that Adobe considers an active user as someone who has used any one of their products more than 4 times in a month. Additionally, Adobe is interested in users who provide reviews with at least 4-star ratings.

Given the following sample data tables for users, usage, and reviews, write a query to find the users who meet the above criteria.

Example Input:
user_idnamesign_up_date
123John Doe05/01/2022
265Jane Smith05/10/2022
362Alice Johnson06/15/2022
192Bob Brown06/20/2022
981Charlie Davis07/01/2022
Example Input:
user_idproductusage_date
123Photoshop06/05/2022
123Photoshop06/06/2022
123Photoshop06/07/2022
123Photoshop06/08/2022
123Photoshop06/09/2022
265Lightroom07/10/2022
265Lightroom07/11/2022
265Lightroom07/12/2022
362Illustrator07/17/2022
362Illustrator07/18/2022
Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022Photoshop4
529336207/18/2022Illustrator5
780226507/12/2022Lightroom4

Answer:


This query first creates a subquery that groups the usage data by user_id for the current month and counts the distinct dates on which each user logged usage. It then joins this with the users table to extract the names of the users. The outer query then checks that there exists at least one review by each user with a rating of 4 or more.

Artist Using Adobe Products

SQL QUESTION 3: What is the difference between cross join and natural join?

Cross join and natural join are like two sides of a coin in the world of SQL.

Cross joins is like the wild and reckless cousin who creates a giant new table by combining every row from table A with every row from table B, no questions asked, no common key needed.

Natural joins are like the more refined and selective cousin who only combines rows from multiple tables if they have something in common (i.e., common columns/keys).

While cross join doesn't discriminate and will create a massive table if given the chance, natural join is more selective and only returns a table with the number of rows equal to the number of matching rows in the input tables. So, choose your JOIN wisely!

SQL Question 4: Average Monthly Subscription Duration

For a company like Adobe, which is known for its various software subscriptions such as Adobe Acrobat, Photoshop, Illustrator and so on, a good question would be to find the average monthly duration of user subscriptions. This would be especially interesting if Adobe wants to know how long users keep using their software on average.

Let's first create some sample tables:

Example Input:
subscription_iduser_idproduct_idstart_dateend_date
100150300101/01/202204/01/2022
100270300201/02/202203/01/2022
100390300101/03/202201/04/2022
1004120300201/04/202201/10/2022
1005200300301/05/202201/08/2022
Example Input:
product_idproduct_name
3001Adobe Photoshop
3002Adobe Acrobat
3003Adobe Illustrator

Answer:

Now let's write a SQL query to get the average subscription duration.


This query first joins the subscriptions and products tables based on the product_id. AVG is used to get the average duration of subscriptions for each product. The DATEDIFF function is used to calculate the length of each subscription in days. By grouping by the product name, this query will return the average subscription duration in days for each Adobe product.

To practice a very similar question try this interactive Facebook Active User Retention Question which is similar for focusing on users' activity over time or this Amazon Average Review Ratings Question which is similar for calculating averages over a certain timeframe.

SQL QUESTION 5: What's a database view, and what's it used for?

Views are a lot like virtual tables, where you can take a base table and customize it (such as by hiding some data from non-admin users, or removing some random columns/rows based on business requirements).

Here's the PostgreSQL syntax for creating a view based on data in the table:


SQL QUESTION 6: How do relational and non-relational databases differ?

While both types of databases are used to store data (no duh!), relational databases and non-relational (also known as NoSQL databases) differ in a few important ways, most importantly on the way data is stored. Relational databases use a data model consisting of tables and rows, while NoSQL databases use a variety of data models, including document, key-value, columnar, and graph storage formats.

This added flexibilty makes NoSQL databases great for non-tabular data (like hierarchal data or JSON data), or data where the type/format is constantly evolving. With this added flexibility, comes one big weakness – you won't get ACID-compliance. That means, unlike relational databases which are typically adhere to the ACID properties (atomic, consistent, isolated, and durable), you don't get as strong guarentees with most non-relational databases.

SQL Question 7: Calculate the average spending of customers

Adobe is interested in analyzing their customer database to understand the buying behavior of their customers. Given a table and an table, write a SQL query to join these two tables together and return the average order value by customers.

Example Input:
customer_idfirst_namelast_nameemailcreate_date
101JohnDoejohn.doe@example.com01/01/2020
102JaneDoejane.doe@example.com02/02/2020
103BobSmithbob.smith@example.com03/03/2020
104AliceJohnsonalice.johnson@example.com04/04/2020
Example Input:
order_idcustomer_idorder_datetotal_amount
20110105/05/2020200
20210106/06/2020300
20310207/07/2020400
20410308/08/2020500
20510409/09/2020600

Answer:


This SQL query joins the table and the table using the field which is common to both tables. The function is used to compute the average total amount of the orders for each customer. The clause is used to group the results by the customer's id, first name and last name. Therefore, the output of the query will show the average spending of each customer.

Because joins come up so often during SQL interviews, try this interactive Snapchat Join SQL question:

Snapchat SQL Interview question using JOINS

SQL Question 8: Calculate Product Rating and Popularity based on Review Data

Adobe wants to analyze how popular each product is and what its average rating is. The popularity is calculated by taking the total product reviews and applying the square root, then rounding to the nearest whole number.

They want to rank products based on popularity and average rating. An important condition is that products with less than 10 reviews should be excluded from the ranking, but their average rating still needs to be calculated.

Assuming you have data structured as shown, write a SQL query that returns a table with columns for , , and .

Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022 00:00:00500014
780226506/10/2022 00:00:00698524
529336206/18/2022 00:00:00500013
635219207/26/2022 00:00:00698523
545589107/12/2022 00:00:00500015
583165408/23/2022 00:00:00698522
666189206/21/2022 00:00:00500011

Answer:


This SQL query first groups the reviews data by and calculates the and for each product. For the , it first counts the number of reviews for each product, takes the square root of that, and then rounds it to the nearest whole number. It applies a condition to only calculate the if there are at least 10 reviews. It calculates the by taking the average of and rounding it to 2 decimal places. Lastly, the results are ordered by (from highest to lowest) and then by (from highest to lowest).

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating average ratings or this Amazon Highest-Grossing Items Question which is similar for ranking products.

p.s. in case this question was tricky, go re-learn SQL with this SQL tutorial for Data Analytics which has 30+ lessons including one on SQL math functions.

Adobe SQL Interview Tips

The best way to prepare for a Adobe SQL interview is to practice, practice, practice. Besides solving the earlier Adobe SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Facebook, Google and unicorn tech startups. DataLemur Question Bank

Each interview question has hints to guide you, fully explained answers along with a discussion board to see how others solved it and best of all, there's an interactive coding environment so you can right in the browser run your SQL query and have it executed.

To prep for the Adobe SQL interview it is also helpful to practice SQL problems from other tech companies like:

However, if your SQL foundations are weak, don't worry about jumping right into solving questions – improve your SQL foundations with this DataLemur SQL Tutorial.

SQL interview tutorial

This tutorial covers SQL topics like RANK vs. DENSE RANK and handling timestamps – both of these come up often during Adobe SQL interviews.

Adobe Data Science Interview Tips

What Do Adobe Data Science Interviews Cover?

For the Adobe Data Science Interview, beyond writing SQL queries, the other types of questions to prepare for are:

  • Probability & Statistics Questions
  • Python or R Coding Questions
  • Data Case Study Questions
  • ML Modelling Questions
  • Behavioral Interview Questions

How To Prepare for Adobe Data Science Interviews?

To prepare for Adobe Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions sourced from tech companies like Google & Microsoft
  • a refresher on Product Analytics, SQL & ML
  • over 900+ 5-star reviews on Amazon

Ace the DS Interview