I would like to aggregate a list of elements where one column is the urgency. I would like to get a row for each item and take the highest “Urgency” observed for that item based on a list (or mapping). Item Urgency A Normal A Low A High B Normal B Low C High Expected output: Item Urgency A High
Tag: presto
Splitting a nested dict-like varchar column into multiple columns using SQL presto
In my table I’ve a column, which is a varchar but has a nested dictionary-like format (three nested levels). Some entries have multiple key-value pairs (customer ID & name), while some just have a single entry (customer ID). For example: I need a query that will break out the the column into a table like this: I know a solution
Extracting timestamp from timestamp with time zone Presto
Is there a native Presto function that provides support to extract the timestamp from a timestamp with time zone? Taking something like this Which returns a value of: 2022-03-13+02:00:99 UTC To: 2022-03-13+02:00:99 I couldn’t find information in the docs for this kind of support. It seems as though my only option is to convert this as a varchar, remove the
How to unpack array as columns
I have a table that looks like this: date volume_info 2022-01-01 {“temple”: 18348, “benny”: 8524, “polly”: 1698, “sally”: 5860} 2022-01-02 {“temple”: 2000, “benny”: 1000, “polly”: 3904, “sally”: 1776, “benjamin”: 2} And I am trying to set it up like this: date temple benny polly sally benjamin 2022-01-01 18348 8524 1698 5860 NULL 2022-01-02 2000 1000 3904 1776 2 But I
Count all records and output values once a condition is met [SQL]
I would like to count all customers and return the signup date for the 3rd customer who has signed up. Essentially evaluate the number of customers that signed up and once the count of customers that have signed up reaches 3 to return the signup date and the id of the 3rd customer sample table output table Answer Use row_number()
Presto lag dates, group/partitioned by id
Say that I want to find every time that a client updated their budget. Here’s what my data looks like And the code I’ve run. What I’m expecting returned will be Hence there are NULL values for dt_2 in the first entry of each client_id. I’m not sure what code will accomplish this effect; is a GROUP BY clause will
Adding hours to dates in Presto
In a Presto db table, I have two string fields, a date of the form, ‘2022-01-01’, and an hour of the form, 22, for 22:00. I’m trying to combine these two elements into a proper timestamp, with date, hours, minutes, seconds. How can I accomplish this? What I’ve tried so far However, I get the error that ‘hour’ is not
AWS Athena: How can we get integer value as string with thousand comma separator in AWS Athena
How can we show integer numbers with thousand comma separator. So, by executing the below statement select * from 1234567890 How can we get the result as 1,234,567,890 Answer You can achieve this by casting number to string and using regex: Output: _col0 1,234,567,890 123,456,789 12,345,678 1,234,567 123,456 12,345 1,234 123
How to concatenate a conditional field and remove the same value
I am trying to create a column with a case statement, then concatenate the column. Here is an example code. Basically, the Action_with_no_date will display the concatenation of values in Action with ‘**’ string added to the values where Date is null for each ID After I did this, I found an edge case. If there is the same Action
Results of an UNION in two columns
I am querying names from two tables: And I get the result I want in a single column. Now I want to know if it is possbile to get the results in two different columns like: “names_from_data1”, “names_from_data2” Or another way to identify where each name comes from: data_1 or data_2 Answer You can add hardcoded column to indicate the