How can I insert a row per group member with a constant value? For example, the following table is a result of ranking hospital visits per user, from earliest to latest. name time lifetime_visit_at_hospital jack 2022-12-02 03:25:00.000 1 jack 2022-12-02…
I have a varchar column {" l l": 2, " m m": 2, " xs xs": 2, " xxs xxs": 2, "s s": 0, "xl xl": 0} How do I filter it to get only keys with values greater then…
I have 2 columns of type int and I want to make a concat to two columns in presto syntax. it is possible? for example: id: 345 997 age: 23 55 new_col: 34523 99755 I was trying to use the…
I have product data in my table which looks similar to this product_id user_id sales_start sales_end quantity 1 12 2022-01-01 2022-02-01 15 2 234 2022-11-01 2022-12-31 123 I want to transform the table into a daily snapshot so that it…
WITH key_value AS ( SELECT e FROM headers CROSS JOIN UNNEST(CAST(json_parse(headers) AS array(json))) t(e) ) SELECT CAST(JSON_PARSE(e) AS map(varchar, varchar)) AS value FROM key_value { "headers": [{ "key": "Name", "value": "xxxx" }, { "key": "Email", "value": "xxxx" }, { "key":…
I have the following query in Athena, I am using S3 as data store. I have 2 query parameters in the lambda expression. PREPARE my_select1 FROM SELECT address.phonenumbers FROM "nested-query-db"."data_with_json1" where cardinality(filter(address.phonenumbers,js->js.type = ? and js.number = ?)) >0 and…
This question is similar to another one, but I'm providing a simpler example. The other query was too advanced to make sense to me. Sample (fake) data id gender kg 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 103 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 85 4f5a07ca-02e0-8981-3c30-4d9924a169a3 male 469 e05d54e9-8292-b26c-5618-8a3712b4fc44…
I need to write a query that sums the values of all possible permutations and shows only one row for the result. I also need to mention that table is stored as an array and that number of items in…
I have a large dataset of URLs that I query in AWS Athena (using PrestoDB). PrestoDB has built-in functions to extract the host of the URL. I would now like to split the host further into subdomain, domain and top-level…
I have a response_data_v0 that contains several duplicates of the UUID values that I want to ignore, keeping just the value that contains the oldest date (In other words, I only want the row with the first appearance of that…