I have some entries where the join date and transaction dates are the same, so I want to include a condition excluding if the count is zero (divide by zero case). I was wondering if I could include …
Tag: amazon-redshift
Adding percentage to number in SQL
I would like to add a percentage sign after a number in SQL. This is the query I have select cast(vetted as float)/cast(account_create as float)*100 from numbers I have tried using the format …
Returning median values of time deltas across different groups
Trying to calculate the range between different steps in my data-table and return the median for each calculation using this SQL code: This returns the following error message: [0A000][500310] Amazon Invalid operation: within group ORDER BY clauses for aggregate functions must be the same; java.lang.RuntimeEx…
Date_Trunc not function working as expected
I am trying to use the Date_Trunc for MONTH function in a SQL statement but somehow it is not working for me. I am trying to pull entries which happen after April 1st, 2019. The raw date format from …
Extracting data from JSON field in Amazon Redshift
I am trying to extract some data from a JSON field in Redshift. Given below is a sample view of the data I am working with. I am able to extract data for the first level namely data corresponding to fileFormat and data as below: I am trying to extract information under data like name, age,dateofbirth Answer Y…
Redshift – CASE statement checking column EXISTS or no
I am querying dynamically tables where some of the tables might not have specific column. My intention is check the existence of the column and dynamically assign a value. Basically if all the tables …
Getting An Error When trying to Calculate two columns
When I run my query I am getting a Precision Error. It is coming from my function after * I tried changing the columns data type to an integer and to_number. it is a varchar. SELECT * , (tableb….
Track customer loss in redshift
I have a data base that looks like this. It has: acct_open_dt, or the day the account was opened. acct_close_dt, or the day the account was closed. dayssinceopendt, which is TODAY – acct_open_dt if …
Redshift – Error when converting UTC time to local time in where clause
I have some sales data that is recorded in UTC. I am trying to convert it to the local timezone where the sales happened. I have built up a query as below but get an error saying invalid operation: …
Alternative to BigQuery for medium-sized data
This is a follow-up to the question Why doesn’t BigQuery perform as well on small data sets. Let’s suppose I have a data-set that is ~1M rows. In the current database that we’re using (mysql) aggregation queries would run quite slow, perhaps taking ~10s or so on complex aggregations. On BigQ…