4,542 questions
0
votes
0
answers
109
views
How to aggregate a group by query in django?
I'm working with time series data which are represented using this model:
class Price:
timestamp = models.IntegerField()
price = models.FloatField()
Assuming timestamp has 1 min interval data,...
- 21
-1
votes
2
answers
190
views
Calculate SUM over a primary key and between dates
My query:
SELECT
c.CustID,
o.OrderID,
SUM(ol.Qty * ol.Price) AS SUMOrder,
AVG(SUM(ol.Qty * ol.Price)) OVER (PARTITION BY c.CustID) AS AVGAllOrders,
COUNT(*) AS Countorders,
SUM(...
- 41
-1
votes
1
answer
167
views
Assign unique values in a set-based approach
Simplifying, I have the following data:
Col1
Col2
A
X
A
Y
A
Z
B
X
B
Y
B
Z
C
Z
I need to receive the following result:
Col1
Col2
A
X
B
Y
C
Z
In other words: For each value in the left column, I need to ...
- 11
0
votes
0
answers
57
views
Polars bug using windowed aggregate functions on Decimal type columns
Windowed aggregate functions on Decimal-types move decimals to integers
I found a bug in polars (version 1.21.0 in a Python 3.10.8 environment) using windowed aggregate functions. They are not ...
- 935
0
votes
1
answer
52
views
BigQuery get rolling average of variable 1 if variable 2 >= quantile
Say I want to get the rolling average of variable x where a second variable y is in the top 5th percentile (over that window).
I can get the rolling average alone with something like this
SELECT
...
- 567
1
vote
1
answer
39
views
How to calculate the maximum drawdown of a stock over a rolling time window?
In quantitative finance, maximum drawdown is a key risk metric that measures the largest decline from a peak to a trough over a period.
I want to calculate the maximum drawdown over the past 10 ...
1
vote
1
answer
122
views
Get a grouped sum in polars, but keep all individual rows
I am breaking my head over this probably pretty simply question and I just can't find the answer anywhere. I want to create a new column with a grouped sum of another column, but I want to keep all ...
- 627
1
vote
1
answer
54
views
Group-By column in polars DataFrame inside with_columns
I have the following dataframe:
import polars as pl
df = pl.DataFrame({
'ID': [1, 1, 5, 5, 7, 7, 7],
'YEAR': [2025, 2025, 2023, 2024, 2020, 2021, 2021]
})
shape: (7, 2)
┌─────┬──────┐
│ ID ┆ ...
- 3,561
1
vote
1
answer
77
views
In PostgreSQL do ranking window functions heed the window frame or act on the entire partition?
I am learning window functions, primarily with this page of the docs. I am trying to categorize the window functions according to whether they heed window frames, or ignore them and act on the ...
1
vote
2
answers
71
views
How to filter sequential event data according to whether record is followed by specific event within X minutes?
I have some data with a timestamp column t, an event category column cat, and a user_id column. cat can take n values, including value A.
I want to select records which are followed (not necessarily ...
- 11
1
vote
1
answer
67
views
Median with a sliding window
The goal is to use MEDIAN as a window function with a sliding window of a specific size.
SELECT *,
MEDIAN(n) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM test_data
ORDER BY id;...
- 181k
1
vote
2
answers
85
views
How to get the max amount per day for a month
I have a table with two columns: demo at db<>fiddle
create table your_table("Date","Count")as values
('2022-01-13'::date, 8)
,('2022-01-18'::date, 14)
,('2022-01-25'::...
- 13
2
votes
2
answers
70
views
Identify duplicates within a period of time using Redshift SQL
In a table, I have plan details of customers with their customer_id and enroll_date.
Now, I want to identify duplicate and valid enrollments from the overall data.
Duplicate: If a customer enrolls a ...
1
vote
1
answer
131
views
How to Exclude Rows Based on a Dynamic Condition in a PySpark Window Function?
I am working with PySpark and need to create a window function that calculates the median of the previous 5 values in a column. However, I want to exclude rows where a specific column feature is True. ...
- 11
1
vote
1
answer
61
views
MySQL filtered gaps and islands: avoiding temporaries and filesorts?
CREATE TABLE `messages` (
`ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`Arrival` TIMESTAMP NOT NULL,
`SenderID` INT UNSIGNED NOT NULL,
-- Fields describing messages skipped
PRIMARY ...
