9 JFrog SQL Interview Questions (Updated 2024)

Updated on

March 3, 2024

At JFrog, SQL is typically used for analyzing software binaries data and managing the databases for their DevOps platform. That's the reason behind why JFrog LOVES to ask SQL questions in interviews for Data Science and Data Engineering positions.

As such, to help you ace the JFrog SQL interview, here’s 9 JfFrog SQL interview questions – able to solve them?

9 Jfrog SQL Interview Questions

SQL Question 1: Identify the Whale Users for JFrog

JFrog is a company that offers DevOps software development tools which improve speed, efficiency, and quality of software development in teams. One of their products is the JFrog Artifactory which is a universal artifact repository manager.

You have been given access to the log tables of JFrog's database. Your task is to write a SQL query to analyze the Artifactory log database to find the whale users. Whale users are those that download a high volume of artifacts very frequently.

Example Input:
log_iduser_iddownload_dateartifact_iddownload_size(in MB)
32412104506/10/2021 00:00:00128794500
34121302406/10/2021 00:00:00898456300
52413104507/11/2021 00:00:00298746700
63524619207/30/2021 00:00:00898456350
77134302408/18/2021 00:00:00128794600

We'll consider a whale user to be anyone who downloaded over 1000MB in any given month.


This query will group the download logs by user and the month in which the download occurred. It then calculates the total download size for each user in that month. The HAVING clause is used to only return users with more than 1000MB of downloads in a given month. These are considered our 'Whale users'. The ORDER BY clause orders the output by total download size in descending order, allowing us to see the biggest 'whales' at the top.

To practice another SQL customer analytics question where you can solve it interactively and have your SQL query instantly executed, try this Walmart SQL Interview Question: Walmart Labs SQL Interview Question

SQL Question 2: Analyzing Software Download Data

As a Data Analyst at JFrog, you are tasked with analyzing the patterns of software downloads across all the JFrog products. You have a table with information about every download made by users from several regions. Each row represents a single download and contains the user_id, product_name, download_date and region.

Write a SQL query to list the total number of downloads each product has had per month, as well as the rank of each product per month based on number of downloads, using SQL window functions.

Example Input:
7291154JFrog Artifactory05/05/2022 00:00:00US
9874786JFrog Pipelines05/15/2022 00:00:00EU
5124447JFrog Artifactory06/12/2022 00:00:00US
7615235JFrog Xray06/18/2022 00:00:00APAC
8349871JFrog Pipelines07/10/2022 00:00:00US
8703339JFrog Xray07/22/2022 00:00:00US
Example Output:
5JFrog Artifactory11
5JFrog Pipelines11
6JFrog Artifactory11
6JFrog Xray11
7JFrog Pipelines12
7JFrog Xray12


This SQL command first extracts the month from the download_date. It then counts the number of downloads for each product per month. The RANK() window function is used to assign a rank to each product per month based on the number of downloads, with the product with the most downloads ranked highest. The result is then sorted first by month and then by rank.

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

SQL Question 3: Can you explain what a cross-join is and the purpose of using them?

A cross-join, also known as a cartesian join, is like a mad scientist's laboratory experiment gone wild. It takes two tables and mixes them together to create a crazy new table with every possible combination of rows from the original tables.

Here's an example:

If you have 20 products and 10 colors, that's 200 rows right there! Cross-joins are great for generating all possible combinations, but they can also create really big tables if you're not careful. Just like a mad scientist, use your powers wisely!

Jfrog SQL Interview Questions

SQL Question 4: Analysis of JFrog Data Transfers

As a Database Lead at JFrog, which is a renowned DevOps Platform, your business often distributes various digital artifacts across the world. Your responsibility is to keep track of these transfers and ensure efficient data delivery. You are given two tables, and .

The table has a row for each artifact transfer, with columns for (unique identifier for each transfer), (identifier for an artifact), (identifier for the source location), (identifier for the destination location), and .

The table has a row for each location, with columns for (unique location identifier), , and .

Design a SQL query to find out the total number of artifacts transferred from each region to each other region in the month of December 2022.

Example Input:
Example Input:
LOC001New YorkNorth America


This query first joins the table with the table twice, once for the source location and once for the destination location. It then restricts the date to December 2022 using the clause. Finally, it groups by both source and destination region and counts the number of artifact transfers between each pair of regions.

SQL Question 5: Can you describe the role of the constraint and provide an example of a situation where it might be applied?

The constraint is used to specify a condition that the data in a column must meet. If a row is inserted or updated and the data in the column doesn't meet the condition specified by the CHECK constraint, the operation will sadly fail.

For example, you might use a constraint to ensure that a column contains only positive numbers, or that a date is within a certain range.

For example, if you had a table of JFrog employees, here's an example of how to use the CHECK constraint in a CREATE TABLE statement:

SQL Question 6: Analyzing Click-Through Rate for JFrog's DevOps Products

As a Data analyst at JFrog, a company renowned for its universal DevOps platform, you are tasked with examining the click-through rate (CTR) of its flagship products. Based on the weblogs data, calculate the Click Through Rate of viewing a product to adding that product to a cart. The Click-through rate is defined as the number of times a product is added to a cart divided by the number of times it is viewed, expressed as a percentage, for each product.

Consider the following sample data from the and tables:

Example Input:
10112407/07/2022 10:22:00view350
10277307/07/2022 10:23:00add_to_cart350
10388007/07/2022 10:24:00view780
10424607/07/2022 10:30:00view780
10524607/07/2022 10:35:00add_to_cart780
Example Input:
350JFrog Pipelines
780JFrog Artifactory


This query calculates the click-through rate as a percentage for each product by dividing the number of times the action 'add_to_cart' appears by the number of times the action 'view' appears for each product_id in the weblogs table. The weblogs table is joined with the products table to provide the names of the products.

Example Output:
JFrog Pipelines50.0
JFrog Artifactory50.0

The column indicates the percentage of views of a product that resulted in adding the product to a cart.

To solve a related SQL problem on DataLemur's free online SQL code editor, solve this SQL interview question asked by Facebook: Meta SQL interview question

SQL Question 7: How do you select records without duplicates from a table?

The keyword removes duplicates from a query.

Suppose you had a table of JFrog customers, and wanted to figure out which cities the customers lived in, but didn't want duplicate results.



You could write a query like this to filter out the repeated cities:

Your result would be:


SQL Question 8: Finding Customers Based on Project Usage

As a data analyst at JFrog, your task is to find all customers who are predominately using a certain project. To do this, you have to filter the customers' usage records database and find records that match the pattern "Maven". Consequently, your task is to write a SQL query that retrieves a list of customers who have used the "Maven" project in over half of their total project activity. Assume that a record is made every time a user interacts with a project.

Example Input:

7001John Smith
7052Sara Johnson
7113William Brown

Example Input:


Note: Each interaction with a project makes a new row in the table, so the frequency of a project can be interpreted as the number of occurrences in this table.

Example Output:

William Brown


For every customer, we calculate two things: the total number of project interactions (COUNT(*)) and the number of interactions with the "Maven" project (SUM of 1's and 0's). If the latter is greater than half of the total, we select that customer's id. After determining the necessary customer ids, we connect back to the customers table to retrieve the full names of these customers.

SQL Question 9: Compute Average Downloads and Round-Off Data Usage

Assume that JFrog has multiple products that clients download on a daily basis. Each download is recorded and the data consumed by each product download is stored as well. Calculate average daily downloads for each product, the total data consumed and find the square root of the total download size (for illustrative purposes). Also, calculate the power of 3 of the daily average downloads and round off the values to two decimal points.

Example Input:
Example Output:
product_idaverage_daily_downloadstotal_data_usedsq_root of_total_data_usedpow_of_average_daily_downloads


This query calculates the daily average downloads by counting the number of downloads and dividing by the number of distinct download dates. The total data used is simply the sum of . The square root of the total data used is computed using the function, and the power of 3 of the daily average downloads is computed using the function and is rounded off to two decimal points using the function.

To practice a very similar question try this interactive Google Median Google Search Frequency Question which is similar for calculating averages and rounding off or this Amazon Server Utilization Time Question which is similar for computing total usage and mathematical operations.

Preparing For The JFrog SQL Interview

The key to acing a JFrog SQL interview is to practice, practice, and then practice some more! Besides solving the above JFrog SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Google, Microsoft and Silicon Valley startups. DataLemur SQL and Data Science Interview Questions

Each DataLemur SQL question has hints to guide you, full answers and most importantly, there's an interactive coding environment so you can instantly run your SQL query answer and have it executed.

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

But if your SQL query skills are weak, don't worry about jumping right into solving questions – improve your SQL foundations with this DataLemur SQL tutorial.

Interactive SQL tutorial

This tutorial covers things like RANK() window functions and math functions in SQL – both of which come up routinely in JFrog interviews.

Jfrog Data Science Interview Tips

What Do JFrog Data Science Interviews Cover?

Besides SQL interview questions, the other topics covered in the JFrog Data Science Interview are:

JFrog Data Scientist

How To Prepare for JFrog Data Science Interviews?

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

  • 201 interview questions taken from FAANG, tech startups, and Wall Street
  • a crash course on Python, SQL & ML
  • over 900+ reviews on Amazon & 4.5-star rating

Ace the Data Science Interview by Nick Singh Kevin Huo