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, long ) into 3 regions. Running 1 query for each region
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 getting OVER 10 YEARS AGO for
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)
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 the location before or not. So, The
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 me error Invalid format: “2021-05-22 04:00:00 +0000” is malformed at “+0000″” Also tried this: throws me error
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 …