How to get the result ‘xxx’ from blanket [ ] in presto. I can’t find the proper regexp to fix my problem Could you help me out? Thank you Answer If you have strings like this: and want to extract substring between square brackets, use this:
Tag: presto
SQL CASE WHEN: is there a limit ( in number of characters )? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 9 months ago. Improve this question I’m using the case when statement to group locations ( starting & destination lat…
How to select whether or not a specific date is before X amount of years? – Presto SQL
I’m trying to select whether or not the date value in one of my columns takes place before a certain amount of years. The code I’m trying out: My expected output should be NULL for the dates I’m using (e.g. 2019-09-30) as they fall within the last 10 years, but for some reason, I’m get…
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…
SQL – How to find if the combination of column has occured before or not?
Following example demonstrates the question id location dt 1 India 2020-01-01 2 Usa 2020-02-01 1 Usa 2020-03-01 3 China 2020-04-01 1 India 2020-05-01 2 France 2020-06-01 1 India 2020-07-01 2 Usa 2020-08-01 This table is sorted by date. I want to create another column, which would tell if the id has been to th…
Error while converting timestamp string with timezone (+0000) to Timestamp in Presto
I am trying to convert a timestamp string to timestamp with date_parse, but keep getting an error. Any suggestions? I am working on Presto SQL. I also refered: http://teradata.github.io/presto/docs/127t/functions/datetime.html, but couldnt find anything that can deal with +0000 i.e Timezone. I tried: throws m…
SQL/Presto: how to choose rows if the values match with another table’s
I have 2 tables: table 1: table 2: I want to add a column for table 2 such that if cnt in table1 for a task is the same as cnt2 for a task in table2. If there is no match, mark it as ‘no match’ desired result: I started from a query like the one below to pick tasks
SQL/Presto: how to rank within a subgroup of each group
I have a table like the following: i want to rank user_id within subgroup of each group by the score and then by time (earlier better) each user_id gets. so the desired output is Answer Use rank(): Actually, I’m not sure if higher scores are better than lower ones, so you might want score asc.
SQL/Presto: rank with multiple conditions (one condition is checking if rows is in the same tag)
I have a data (dt) like the following: I want to rank them within a group, first by score (lower score is better) then concat customer_id if the 2 customers are in the same group with the same score, and same tag (NOT null) I need to rank them by phase (older phase is preferred) to produce a final list.
Attach the most recent timestamp when value was changed to each row
I have a dataset of updated properties: user_id flag updated_at 123 1 101 123 0 101 123 1 102 123 1 103 123 1 104 124 0 101 124 0 102 124 0 110 I need to assign to each row a value when was …