I have a table with Forecast, Budget, & Actuals. I need to grab the latest data based on the month which is prefixed in the d_period field (Ex: Jan, Feb, Mar, Apr, etc.). The query should always default to the latest month (no relation to current month – the upload varies so it could be 2 months ago…
Tag: presto
Convert to string to ‘Proper’ casing
Is there a way to convert a string to ‘Proper’ casing? I’m using the Excel definition of ‘Proper’ which will format text such that the first letter of any word is capitalized and the remaining letters are lower case. Sample Inputs | Outputs I browsed the string function/operators…
Presto – How to convert a field with map to rows
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”: I want to pull all order_id(s) and convert them as rows, the output may look like this: My query: I tried the query above but it can only returns the order_id as an array.
Querying latest snapshot partition with Athena
I have a partitioned table with daily snapshots from from glue. When I use athena to query it queries across all partitions. Is there a way to get Athena to automatically only get the latest snapshot? Or do I have to explicitly state what partition I want to query if I want to avoid querying across all snapsh…
Get JSON object keys as array in Presto/Trino
I have JSON data like this in one of my columns and I would like to run a query that returns the keys as an array Answer Convert your JSON into a MAP and then use map_keys(): Use json_parse() if your JSON column is of type VARCHAR Output: _col0 bar, foo foo
Extracting a string from a nested dictionary in presto
I have data that looks like this: I’d like to extract “subquestions” into separate rows (in an sql table): — Making ads for Meta platforms — Producing video content — Applying mobile creatives best… I have a tried a few functions json_extract_scalar, and Map functions…
Presto SQL save query results in variable
I have a database that I am querying with athena. I am using subqueries to select a subset of the data like so can I save the query results of in a variable VAR so that we need not query it again and again and also to make query look cleaner? Answer There is no such concept as variable in
Convert string date format MM/dd/yyyy HH:mm:ss.SSSS to timestamp presto
I have a string that looks like: I am trying to convert this to a timestamp as follows: but i keep getting an error: Answer The value in your example matches the format of timestamp literals in SQL, so you can convert it to a timestamp type by just casting the value to the desired type:
Missing dates for specific identifiers without adding extra dates when this identifier is no longer in the database SQL
To put the problem in words, I have a massive table which includes subscribers and data for every day. If the subscriber no longer exists, then they will have no more records i.e. SUB123 no longer exists from the 28/10/2021 then this subscriber will have records up every day until 27/10/2021. The problem at h…
SQL/Presto expanding a list column into multiple rows
Given a hive table like How can I create a secondary table like Answer This is a textbook case for unnest: Output: a bb 1 1 1 2 1 3 2 2 2 3