In AWS Timestream I am trying to get the average heart rate for the first month since we have received heart rate samples for a specific user and the average for the last week. I’m having trouble with the query to get the first month part. When I try to use MIN(time) in the where clause I get the error:
Tag: sql
How to recursively calculate rows based on previous rows in Snowflake?
I’m trying to build a performance score on each player in my table and use this to run some analysis. I’ve tried to use the Elo ranking using the player’s score in the game vs themselves in the prior game. The game is a time-trial race so the faster they complete the better their performance…
Enforce 2 unique constraints on upserts in Postgres
I have a table where I save contacts data I add data to this table from several sources. Some sources have Linkedin profiles data about people, other sources have email data. Sometimes the full names are not equal, even if they refer to the same person. And I want to do upserts to not have duplicated data. Fo…
How do I add multiple values to a column in SQL which already has other rows filled
I’m trying to update this table (name: sports_club): I’m trying to add values to just the sport_interest column of the existing records, I tried to do that by using: But it did not work as it was trying to add it to new records which I don’t want to do. I’m using MySQL Answer You can d…
How to change my name into columns using sql
What is the best query to split name into columns? ie. if my name is VELOCITY in one column. How can i split it as Answer If you just have a single row then: Which outputs: CH V E L O C I T Y If you have multiple input rows then, from Oracle 12, you can use a LATERAL
Repeating JSON Aggregated Array with Row Limits
I have an Oracle 19 database with a JSON aggregated array which I need to cut off and repeat after a set number of rows, for example: …etc. Here’s some basic SQL to illustrate how my current dataset is put together: I need to push customer data to an API endpoint. Pushing 1 customer record at a ti…
PSQL: How to compare record to previous record (2 tables)?
I have two tables: 01_System_Log Records I need to create a view, where the records of the last request id (3) are compared with the previous (2). So far I made a view, where the most actual records are shown. I am not sure how to proceed, while in all compare examples, which I have found, only one table was
DB2 functions returning error SQL CODE 4743?
I’ve been trying to solve this issue now for a while. I have a table called Students like: I am using DBVisualizer right now to execute my statements, but I am trying to play around with something called LISTAGG() as a DB2 function: However, every time I try to run this, I get this error: And I have no …
Using if-then or case-when-then to select from dataset
I have a dataset with students in 1st, 2nd and 3rd grades. I want to select the names of all students in 1st and 2nd grades, but only students named John, Jane or Smith from 3rd grade. Here is what I have so far: I’m not sure what I’m getting wrong there but I’d appreciate some help. Thanks …
SQL query to find user by it’s specific latest action
I have following db scheme below where all user actions are collected: The goal is to find all recently blocked users. So the expected result is to find 1 and 3 user ids since those users were recently blocked. I’ve tried to play with following SQL below, but still do not have good understending how to …