I am trying to convert the following Presto SQL to Hive SQL. The sub query in WHERE clause isn’t working in Hive and is throwing an error. select distinct id from account where load_date = (select max(load_date) from account) and account_status=’current’ and account_type=’crnt’ Please advise how I can fix this and make it work in… Read More Error while converting Presto SQL to Hive SQL containing subquery

I have table in Presto like below: Input data: PHONE_NUMBER ——– ppa:+77845678021@abc.gbc443.gbc356.7kffooopppp.deu tel:+77695002395 dadsadas+884415002996xx2 …. Requirements: And I need to take all values from column "PHONE_NUMBER" between "+" and "@" (if exists). Desire output: So, as a result I need something like below: PHONE_NUMBER ——– 77845678021 77695002395 884415002996 …. How can I do that in… Read More How to extract string between "+" and "@" from column in Presto?

Below is the base table of data.. date customer_id score1 score2 01/01/22 a 1 1 02/01/22 a 1 1 01/01/22 b 2 2 02/01/22 b 4 1 01/01/22 c 1 1 02/01/22 c 1 4 01/01/22 d 5 1 02/01/22 d 10 1 This is the result that I want to achieve, where I only… Read More Get minimum per row based on value from another column

I have a table with 5 columns, For each unique combination of the first three columns, I want a single sample row. I don’t care which values are considered for columns 4 and 5, as long as they come from the same row (they should be aligned). I realise I can do a DISTINCT to… Read More Get one row per unique column value combination (`DISTINCT ON` operation without using it)

There is a column in a string format. Data in that column is a mixture of letters and numbers. I want to pick up rows which contain more than 4 numbers in it. For example, in the table below, what I’m interested in is row_num 4 as it contains more than 4 numerical values. How… Read More How to count the number of numerical values in a string

I’m having a bit of trouble with some Presto SQL that I have written in Athena. I get the following error which I’m a bit confused about: SYNTAX_ERROR: line 46:39: ‘+’ cannot be applied to varchar, varchar Here is my script: SELECT Duplicate.AircraftTypeCode, Duplicate.LineNumber, Serial.SerialNumber FROM (SELECT * FROM (SELECT DISTINCT TypeCode AS AircraftTypeCode, LineNumber… Read More AWS Athena (Presto) '+' cannot be applied to varchar, varchar error

I have a query that shows me the earnings for each shop_id and country as below. select shop_id, country, start_date, sum(earnings) as earnings from x where country IN (‘DE’, ‘IT’, ‘ES’) group by 1,2,3 However, I want to have only three shops in the country DE and all shops in the rest of the countries.… Read More How to filter query in SQL conditionally?

I’m new to Presto SQL and I am stuck to a issue and I really need your help. I have a table with a field "Info": Date Info 2021-01-01 {"user_id":"12345", "order_id": "[333222, 444555, 777888]"} 2021-01-02 {"user_id":"67891", "order_id": "[948263]"} 2021-01-03 {"user_id":"93846", "order_id": "[937846, 889213, 886534, 991827]"} 2021-01-04 {"user_id":"63792", "order_id": "[]"} 2021-01-05 {"user_id":"04937", "order_id": "[837462, 837213]"} I… Read More Presto – How to convert a field with map<string,string> to rows

I need to access to the elements in a column whose type is list according to the other elements’ locations in another list-like column. Say, my dataset is like: WITH dataset AS ( SELECT ARRAY [‘hello’, ‘amazon’, ‘athena’] AS words, ARRAY [‘john’, ‘tom’, ‘dave’] AS names ) SELECT * FROM dataset And I’m going to… Read More Athena query get the index of any element in a list

I have 2 tables, TABLE1: job job_type job_id start_time end_time hello EXPRESS 125445801 6/9/22 10:59 6/9/22 11:59 hello EXPRESS 125449030 6/9/22 11:19 6/9/22 11:38 hello EXPRESS 125444620 6/9/22 10:59 6/9/22 11:11 and a TABLE2: job time_sample hello 6/9/22 10:59 hello 6/9/22 11:13 hello 6/9/22 11:21 hello 6/9/22 11:49 The issue is on the first table.… Read More Join with overlapping times using sql?