Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [amazon-presto]

Use for questions about how to use Amazon Presto SQL query engine on Amazon EMR.

Taken from the docs for Prest:

Presto on Amazon EMR is a SQL query engine optimized for low-latency, ad-hoc analysis of data. It supports the ANSI SQL standard, including complex queries, aggregations, joins, and window functions. Presto can process data from multiple data sources including the Hadoop Distributed File System (HDFS) and Amazon S3.

8 questions
3
votes
1 answer

Getting the row before a row with a certain value in SQL

I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of those actions to see which actions happens before the…
Daniel Müller
  • 133
  • 1
  • 4
1
vote
3 answers

Presto/Mysql join validation

I have 2 tables, and the CASE statement below should only evaluate to 1/true if the record with the same lead_id exists in both tables. Here I have 2 ways of achieving this but just want to verify if its in fact the most optimal way of achieving…
code-is-life
  • 117
  • 1
  • 4
1
vote
1 answer

Rolling sum with a floor in Athena/Presto

In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values (1, 2, -1, -2, -1, -2, 1, 2) I should get (1, 3, 2, 0, 0, 0, 1, 3). Without the floor constraint, it's easy -…
Max Gibiansky
  • 111
  • 1
  • 3
1
vote
1 answer

(Athena/Presto) Window function (lag) is not reading the previous lag row

I have two tables, credit_memos and credit_memo_items and want to show each event individually and will thus do a insert (doing union in this case which is logically the same). I know the key for both tables and am doing a union with them while…
Eugene Choi
  • 11
  • 1
  • 5
1
vote
1 answer

AWS Athena: syntax for grouping data for week ending on a Saturday

I'm having some issues with the Presto syntax in grouping data by separate weeks, ending on Saturdays. I have this field called "timestamp," and I've been using the the following to group by weeks. However, I noticed that date_truncs weeks ending on…
Jamal
  • 11
  • 1
  • 2
1
vote
1 answer

SQL query - Do I need a full join to produce this combined result?

With two tables structured like this: Table1 Table2 ---------------- ---------------- id, time, x id, time, y, z 1 1 1 1 2 2 2 1 2 2 1 3 3 …
Jordan
  • 13
  • 4
1
vote
1 answer

How to query from the result of a changed column of a table in postgresql

I am working with Presto on Amazon Athena. I have a string time column in a table and now I want to change that time to date time type and then query data for selected dates. Is there a direct way to do so? One way I could think of is 1) add a new…
Atihska
  • 129
  • 1
  • 6
0
votes
2 answers

PRESTO Left join using multiple operators

I have 2 tables, one listing all our customers and then a 2nd listing every clients list of creditors/obligations Firstly, I need to return all clients in t1 and then be able to determine if the client owns a vehicle or not by looking at t2 This…
code-is-life
  • 117
  • 1
  • 4