Skip to content

Tag: amazon-web-services

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, pattern, function)

Redshift : Coalesce Can’t Work As Expected

We’re complementing null value to all zero value like ’00’ on Redshift. Sometimes, I found coalesce function can’t work as we expected. If we use case and len, it can work fine as follows; So far, I have no idea why coalesce can’t work. Any advice would be appreciated. Thank you. Answer There is a difference between ” and NULL

Redshift Postgres 8

I’m trying to write a query to solve a logical problem using Redshift Postgres 8. Input column is a bunch of Order IDs and Step Group IDs and desired output is basically a sequence of the IDs as you can see in the screenshot. If you could help me answer this question, that would be great, thanks! This is a

How best can I to pivot my redshift table?

I have a Redshift table called metadata with the following columns: id key value 1001 code1 my value 1001 code2 another 1001 code3 yet another 1002 code1 new one 1002 code2 here 1002 code3 last 1003 code1 hello 1003 code2 goodbye 1004 code2 now 1004 code3 then I’d like to have a query that return it as: id code1 code2

Calculate Median for each group in AWS Athena table

Below is the schema for the athena table I wish to calculate median for ‘parameter_value’ group by standard_lab_parameter_name & units. For this I followed link : But on running the query It throws error Any help? Or if some alternative query would be great Answer Athena is based on Presto 0.172 – you can see all supported functions in