I have a dataset of subscriptions in a table that looks something like this: Table: UserID Subscription_type Subscription_end_date I’m trying to build a report in SSRS that shows a list of all subscriptions that are going to expire somewhere in the next month. The report gets sent on the first of every …
Proper Case in Athena SQL
I’m new to Athena and trying to convert a field ‘Country’ to Proper Case. For example: united states -> United States Is there a way to do this in Athena? Answer Turning a comment into an answer: Athena engine version 2 gives in its RegEx section as example for regexp_replace(string, patt…
How to remove certain results from my search count?
I received a few irrelevant results to my count that I would like to remove from my output. When using only the first “where lower (person) not like ‘%c%’” worked but when adding person b, c …
fetchall() not working even with values in database
I am working in python with sqlite3 and I am trying to create a database. I created a table: c.execute(“CREATE TABLE persoane (nume text, varsta integer, salariu integer)”) Then I added the values into the table: c.execute(“INSERT INTO persoane VALUES(‘David’, 16, 1000)”) W…
Update data based on the previous row in postgresql
For rn (row number) = 1, x and y always stays as 0. Now I need to update the rest of x and y columns based on the above scenario(IF ..ELSE). I am unable to update the next row without updating the previous row. Is there a way to do this in a query Answer You can use a recursive
How to order SQL query by largest string array sizes?
Background I’m quite new to SQL so apologies if I come off ignorant. After reading through some related threads I am still confused. I got the data from a Firestore export whose endpoint was a Google Cloud Storage bucket. I created a table from the bucket data in BigQuery. Question I want to order the q…
Excluding same type of transactions within 3 seconds
For example, a table is like below Type Time Stamp Result 1 2021-06-25 14:21:00 A 1 2021-06-25 14:21:03 B 1 2021-06-25 14:21:06 C 1 2021-06-25 14:23:00 D 2 2021-06-25 14:21:02 C 2 2021-06-25 14:21:06 C 2 2021-06-25 14:21:09 D 3 2021-06-25 14:21:06 E And the result I want is as below. If there are a series of …
Oracle 11g SQL Query – Specify which duplicates to Exclude from Select Statement
I have a question about excluding duplicate values with my Oracle SQL query. Using select distinct does not work in this case. I only want to select one row for each event#, the row where Mobile = Yes. Actual Result of Current Query: Desired Result: In this ideal result, one of the rows for Event# 1 has not b…
Redshift SQL: Column Counting Matching Rows Given a Condition within another column
I have a table with two columns, user ids and strings. I want to add a third column that counts the number of strings within the second column that start with the entire string value in any given row. There is only one row per user. The goal is to get the following table structure: Here the count is equal
Checking to see if array field is null or empty?
In snowflake, how can I filter for null or empty array fields in a column? Column has an empty [] or string of values in that bracket, tried using array_size(column_name, 1) > 0 but array_size does not function, Thanks Answer Are you trying to filter them in or out? Either way the array_size should work. a…