Documentation : https://prestodb.io/docs/current/functions/datetime.html I have epoch timestamps from which I want to extract week of the year like 2021-32, 2020-50, 2021-02 and so on. However I am getting up some wrong values like : week = 2021-53 for Epoch-Time corresponding to Jan 1, 2021 or Jan 2, 2021. I understand that there is a sync issue happening here but that
Tag: presto
SQL – Guarantee at least n unique users with 2 appearances each in query
I’m working with AWS Personalize and one of the service Quotas is to have “At least 1000 records containing a min of 25 unique users with at least 2 records each”, I know my raw data has those numbers but I’m trying to find a way to guarantee that those numbers will always be met, even if the query is
Count ROW type item Athena / Presto
I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date
SQL many-to-one join – how to get blank fields instead of duplicated values
I have 3 tables: When I join this tables by ticket_id and query, i get result like this: But I want to blank duplicated values and get result like this: Is it possible to get result set like this? I’m working in AWS Athena(Presto SQL), but I would really appreciate your ideas/suggestions on this regardless of the dbms. Thanks! Answer
Query validation for Amazon Athena using AWS SDK
I am using the AWS SDK to execute Amazon Athena queries using Java and what I would like to do is to have some way of ensuring only SELECT queries are executed. This is to make sure queries like DROP or INSERT are not executed. So is there any way I can perform this check using the AWS SDK without
SQL query to find the second occurrence of an element in a database
For example I have this table: I want to get the row where the PersonID occurs for the second time. So the desired output needs to be (for one of the person ID) : What would be the query for this scenario? Answer Use ROW_NUMBER() Function For this scenario Please Refer this link for how to use this function different
How to join two tables while only selecting the highest day of each month from one table
I have two tables. One with metadata, one with billing data. I need to join those effiently in order to assign metadata to costs. Table 1 (metadata) looks like this: Table 2 (billing data) looks like this: For each combination of year, month, id in Table 2, there is an corresponding ID in Table 1. For each year, month, id
Casting Decimal to Currency in AWS Athena
I have a field value(decimal(23, 2)) and I am querying over it like this: sum(value) and as output i get 1200000.32 I am trying to cast it to currency to get $1.200.000,32 like this: How can I get the desired currency type? Answer There is no money type in presto. And Amazon’s version of presto does not support format and
Presto – Convert Table into Map
I would like to convert the below table into a map with multiple key/value pairs label_a label_b 1 2 3 4 5 6 into {‘label_a’ -> 1, ‘label_b’ -> 2} {‘label_a’ -> 3, ‘label_b’ -> 4} {‘label_a’ -> 5, ‘label_b’ -> 6} What would be the easiest way to do this? All the results I’ve found are scala/non-presto solutions and
generate date range between min and max dates Athena presto SQL sequence error
I’m attempting to generate a series of dates in Presto SQL (Athena) using unnest and sequence something similair to generate_series in postgres. my table looks like I’m aiming for an output as follows I’ve attempted to use the following query to achieve this – however I get an error when trying to unnest my date sequence which yields the following