Tag: presto

Grab Latest Data Based on String

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, 4 months

Convert to string to ‘Proper’ casing

presto sql

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 documentation so it seems like this isn’t possible, but hoping someone

Presto – How to convert a field with map to rows

arrays presto sql

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

amazon-athena amazon-web-services aws-glue presto sql

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 snapshots? Answer If

Get JSON object keys as array in Presto/Trino

jsonpath presto sql 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

dictionary nested presto sql

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 etc, but I didn’t succeed. Thanks in advance for your help. So the thing I

Presto SQL save query results in variable

amazon-athena presto sql

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

datetime presto sql

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

presto 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 hand is that some subscribers have