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…
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 o…
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, “benjam…
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 An…
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…
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 g…
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 …
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 ca…
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 Ans…