All Questions
Tagged with redshift window-functions
6 questions
0
votes
1
answer
2k
views
Having issue with window function COUNT counting duplicates
I have a query like so:
select
distinct(line_items.email) as email,
count(orders.order_id) over(partition by orders.order_id) as num_orders
from
line_items
inner join orders on
...
- 153
1
vote
1
answer
321
views
How to force Redshift/Postgres to aggregate nth_value?
I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:
select
customer_email,
nth_value(created_at, 1) over (partition by ...
- 153
2
votes
0
answers
81
views
Efficient way to create daily summary table
I'm trying to create a daily summary table that captures characteristics of each client known as of every given date that my company has been in business. The challenge is that there are so many ...
- 121
5
votes
1
answer
2k
views
Understand why rank() over doesn't fit to don't select duplicates rows
I would like to understand why I have different results
I have a table called active_transfert where I log image transfert
user_id | image_id | created_at
--------|----------|-----------
1 |1 ...
- 651
0
votes
1
answer
875
views
How do you select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old?
I need help writing a SQL query for Redshift (Postgres will do) likely involving WINDOW functions, PARTITIONS, LAST_VALUE and other things above my head.
Users can submit survey responses at any ...
4
votes
2
answers
442
views
Finding earliest connected value over two columns
I have a, perhaps slightly esoteric, use case.
Given a table like the following:
id1 | id2 | timestamp
-----+-----+---------------------
3 | 4 | 2016-03-22 09:52:15
1 | 2 | ...
