Can window functions be used without aggregate functions?

Hi there :smiley_cat:

I am going through the Window Functions lesson of the Analyze Data with SQL skill path, and I have a question.

The definition of a window function in the lesson is this one:

Window functions , on the other hand, allow you to maintain the values of your original table while displaying grouped or summative information alongside in another column.

Another definition I found:

Window functions applies aggregate and ranking functions over a particular window (set of rows).

After a lot of thought (and Google searches), I thought I had gotten the hang of it.
So I went back to the Codecademy lesson and tried writing my own Window Function, but without an Aggregage function. It didn’t work. And I believe I did everything else correctly, cause when I changed it to include an aggregate function it did work.

So my question is, can window functions only be used with aggregate functions?

Hello,

In the lesson that you linked, a few of the exercises demonstrate the use of window functions that don’t use aggregates, I believe.

Exercises:

  1. FIRST_VALUE and LAST VALUE
  2. LAG
  3. LEAD
  4. ROW_NUMBER

etc. etc.

I think the ROW_NUMBER one illustrates it well because it doesn’t use any GROUP BY at all. It’s providing the row number based on the sort order.

I like the Window Function Cheat Sheet from here for a quick reference. It includes a list of window functions by category: Ranking, Distribution, Analytic, Aggregate

What kind of things were you trying to do?

3 Likes

Oh, my bad. I haven’t gotten to those parts of the lesson :woman_facepalming:

Thanks, I’ll make sure to check that one out.

Well, I was in the middle of making notes about Window Functions cause I thought I had already understood them quite well (funny, considering I hadn’t even finished the lesson). I started using an example of a people_skills table, which is a very basic table with a column for the person’s name and another one for the skill they have. Each person can have more than one skill.

I thought with window functions I could manage to show all the skills each person has in a single result (as opposed to getting just one skill per person using a GROUP BY). Just to verify something like that could be done, I went to the lesson and wrote this query:

SELECT username, posts
   OVER (PARTITION BY username) AS 'posts'
FROM social_media;

I wasn’t really trying to get an useful result (lol), but I thought this query would output the number of posts each user had in each month. It didn’t output anything, so I guess something went wrong, but I didn’t even get an error message :woman_facepalming:

The lack of error messages is definitely a pain point when working on SQL queries from within the Codecademy learning environment. I notice that the exercises typically save the results to a file named output.txt, but there isn’t any kind of error log.

1 Like

Totally agree with you. Not getting any clues of why my queries aren’t working is really annoying.

Thanks a lot for the cheat sheet you provided, it truly has a lot of useful information :smiley_cat:
Also, I just figured out (idk why I didn’t before) that the result I wanted to get could be obtained using a simple JOIN. No need for a window function.

I think I get it now: window functions let you apply functions to your data that would normally compact it to one row per group…without compacting it (it shows you all the rows per group). That allows you to get some interesting results (like running totals for certain columns).