Skip to content

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

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

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

Extracting timestamp from timestamp with time zone Presto

Is there a native Presto function that provides support to extract the timestamp from a timestamp with time zone? Taking something like this Which returns a value of: 2022-03-13+02:00:99 UTC To: 2022-03-13+02:00:99 I couldn’t find information in the docs for this kind of support. It seems as though my only option is to convert this as a varchar, remove the

How to unpack array as columns

I have a table that looks like this: date volume_info 2022-01-01 {“temple”: 18348, “benny”: 8524, “polly”: 1698, “sally”: 5860} 2022-01-02 {“temple”: 2000, “benny”: 1000, “polly”: 3904, “sally”: 1776, “benjamin”: 2} And I am trying to set it up like this: date temple benny polly sally benjamin 2022-01-01 18348 8524 1698 5860 NULL 2022-01-02 2000 1000 3904 1776 2 But I