# Find A Closed Formula For The Generating Function 7 N SQL Window Functions on Data Science Interviews Asked By Airbnb, Netflix, Twitter, and Uber

You are searching about Find A Closed Formula For The Generating Function 7 N, today we will share with you article about Find A Closed Formula For The Generating Function 7 N was compiled and edited by our team from many sources on the internet. Hope this article on the topic Find A Closed Formula For The Generating Function 7 N is useful to you.

Muc lục nội dung

## SQL Window Functions on Data Science Interviews Asked By Airbnb, Netflix, Twitter, and Uber

Window functions are a group of functions that perform calculations on a set of rows corresponding to your current row. They are considered advanced sql and are often asked during data science interviews. It is widely used in this work to solve many types of problems. 4 Let’s summarize the different types of window functions and cover why and when you use them.

4 types of window functions

1. Regular aggregate operations

o These are aggregates like AVG, MIN/MAX, COUNT, SUM

o You want to use it to aggregate your data and group it into another column like month or year

2. Ranking functions

o ROW_NUMBER, RANK, RANK_DENSE

o These are functions that help you determine your data level. You can either categorize your entire dataset or sort by groups such as month or country

o Extremely useful for generating sorted indexes on groups

3. Generating statistics

o These are great if you need to generate simple statistics like NTILE (Percentile, Quartile, Median).

o You can use it for your entire dataset or by group

4. Handling time series data

o A very common window function especially if you need to calculate a trend like a month-over-month rolling average or growth metric.

o LAG and LEAD are two functions that allow you to do this.

1. Regular composite function

Regular aggregate functions are functions such as average, count, sum, min/max that are applied to columns. Applying the aggregate function is the goal if you want to apply aggregations to different groups in a dataset such as month.

This is similar to the type of calculation that can be performed with an aggregate function that you find in a SELECT clause, but unlike regular aggregate functions, window functions do not group multiple rows into a single output row, they are grouped together or maintain their own identity depending on how you find them. .

average() Example:

Let’s look at an example of the average() window function applied to answer a data analytics question. You can view the question and write the code in the link below:

platform.stratascratch.com/coding-question?id=10302&python=

This is a great example of using the window function and then applying average() to a month group. Here we are trying to calculate the average distance per dollar per month. This is difficult to do in SQL without the window function. Here we have applied the avg() window function in the 3rd column where we have found the average value for month-year for each month-year in the dataset. We can use this metric to calculate the difference between the month average and the date average for each request date in the table.

The code to implement the window function looks like this:

select a.request_date,

a.dist_to_cost,

AVG(a.dist_to_cost) OVER(PARTITION BY a.request_mnth) AS avg_dist_to_cost

from

(select *,

to_char(request_date::date, ‘YYYY-MM’) as request_mnth,

(distance_to_travel/monetary_cost) AS dist_to_cost

from uber_request_logs) a

Order by request_date

2. Ranking functions

Ranking functions are an important utility for the data scientist. You are always ranking and indexing your data to better understand which rows are the best in your dataset. SQL window functions provide you with 3 ranking utilities — RANK(), DENSE_RANK(), ROW_NUMBER() — depending on your exact use case. These functions will help you sort and list your data into the groups you want.

Rank() example:

Let’s look at a ranking window function example to see how we can classify data into groups using SQL window functions. Follow along interactively with this link: platform.stratascratch.com/coding-question?id=9898&python=

Here we want to find the top salaries by department. We cannot find the top 3 salaries without the window function because it will give us the top 3 salaries in all departments, so we need to sort the salaries by departments individually. This is done by category () and is divided by department. From there it’s really easy to filter for the top 3 in all departments

Here is the code to output this table. You can copy and paste the above link into SQL editor and see the same output.

selection department,

salary,

RANK() over (split by department

ORDER BY salary DESC) AS rank_id

from

(Selection Dept., Salary

Group by department, salary

Order by Department, Salary) a

Order by Department,

Salary DESC

3. NILE

NTILE is a very useful function for those in data analytics, business analytics, and data science. Often times when deadline with statistical data, you need to create robust statistics like quartile, quintile, median, decile in your daily work, and NTILE makes it easy to generate these outputs.

NTILE takes an argument of the number of bins (or basically how many buckets you want to divide your data into), and then creates this number of bins by dividing your data into a number of bins. If you want more groups, you set how the data is sorted and divided.

NTILE(100) example

In this example, we’ll learn how to use NTILE to sort our data into percentages. You can follow along interactively at the link here: platform.stratascratch.com/coding-question?id=10303&python=

What you want to do here is identify the top 5 percent of claims based on an algorithm output. But you can’t just find the top 5% and order because you want to find the top 5% by state. So one way to do this is to use the NTILE() ranking function and then PARTITION by state. You can then apply a filter in the WHERE clause to get the top 5%.

Here is the code to output the entire table above. You can copy and paste it from the link above.

select policy_number,

state,

claim_cost,

fraud_score,

percentage

from

(select *,

NTILE(100) over (split by state

ORDER BY fraud_score DESC) as percentage

FROM fraud_score) a

where percentile <=5

4. Handling time series data

LAG and LEAD are two window functions that are useful for dealing with time series data. The only difference between LAG and LEAD is that you want to follow previous rows or rows, almost like sampling from previous data or future data.

You can use LAG and LEAD to calculate month-to-month growth or rolling averages. As a data scientist and business analyst, you are always dealing with time series data and creating metrics over time.

LAG() example:

In this example, we want to find the year-over-year percentage growth, which is a very common question that data scientists and business analysts answer on a daily basis. The problem statement, data, and SQL editor are at the following link if you want to try coding the solution yourself: platform.stratascratch.com/coding-question?id=9637&python=

What’s difficult about this problem is the data set up — you need to use the previous row’s value in your metric. But SQL isn’t built to do that. SQL is built to calculate anything you want as long as the values ​​are in the same row. Then we can use the lag() or lead() window function which will take the previous or later rows and put it in your current row that it’s querying.

Here is the code to output the entire table above. You can copy and paste the code into the SQL editor at the link above:

Select the year,

current_year_host,

prev_year_host,

round(((current_year_host – prev_year_host)/(cast(prev_year_host AS numeric)))*100) estimated_growth

from

(Select year,

current_year_host,

LAG(current_year_host, 1) OVER (order by year) as previous_year_host

from

(extract (year

FROM host_since::date) AS year,

count(id) current_year_host

from airbnb_search_details

WHERE host_since NOT NULL

Group by withdrawal (year

FROM host_since::date)

Order by year) t1) t2

## Question about Find A Closed Formula For The Generating Function 7 N

If you have any questions about Find A Closed Formula For The Generating Function 7 N, please let us know, all your questions or suggestions will help us improve in the following articles!

The article Find A Closed Formula For The Generating Function 7 N was compiled by me and my team from many sources. If you find the article Find A Closed Formula For The Generating Function 7 N helpful to you, please support the team Like or Share!

Rate: 4-5 stars
Ratings: 8047
Views: 72158750

## Search keywords Find A Closed Formula For The Generating Function 7 N

Find A Closed Formula For The Generating Function 7 N
way Find A Closed Formula For The Generating Function 7 N
tutorial Find A Closed Formula For The Generating Function 7 N
Find A Closed Formula For The Generating Function 7 N free