SQL Programming Resources

SQL INTERVIEW Questions

Explain the concept of window functions in SQL. Provide examples to illustrate their usage.

Answer:

Window Functions:
Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not group rows into a single output row; instead, they return a value for each row in the query result.

Types of Window Functions:
1. Aggregate Window Functions: Compute aggregate values like SUM, AVG, COUNT, etc.
2. Ranking Window Functions: Assign a rank to each row, such as RANK(), DENSE_RANK(), and ROW_NUMBER().
3. Analytic Window Functions: Perform calculations like LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE().

Syntax:

SELECT column_name, 
       window_function() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Examples:

1. Using ROW_NUMBER():
Assign a unique number to each row within a partition of the result set.

   SELECT employee_name, department_id, salary,
          ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
   FROM employees;
   

This query ranks employees within each department based on their salary in descending order.

2. Using AVG() with OVER():
Calculate the average salary within each department without collapsing the result set.

   SELECT employee_name, department_id, salary,
          AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
   FROM employees;
   

This query returns the average salary for each department along with each employee's salary.

3. Using LEAD():
Access the value of a subsequent row in the result set.

   SELECT employee_name, department_id, salary,
          LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
   FROM employees;
   

This query retrieves the salary of the next employee within the same department based on the current sorting order.

4. Using RANK():
Assign a rank to each row within the partition, with gaps in the ranking values if there are ties.

   SELECT employee_name, department_id, salary,
          RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
   FROM employees;
   

This query ranks employees within each department by their salary in descending order, leaving gaps for ties.

Tip: Window functions are powerful for performing calculations across a set of rows while retaining the individual rows. They are useful for running totals, moving averages, ranking, and accessing data from other rows within the same result set.

Go though SQL Learning Series to refresh your basics

Share with credits: https://t.me/sqlspecialist

Like this post if you want me to continue SQL Interview Preparation Series 👍❤️

Hope it helps :)

1. What is the difference between the RANK() and DENSE_RANK() functions?

The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.

2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?

One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.

3. What is the shortcut to add a filter to a table in EXCEL?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.

4. What is DAX in Power BI?

DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.

5. Define shelves and sets in Tableau?

Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.

SQL Interview Questions with Answers

Like for more ❤️