I need all entries from Table A and all entries from Table B that are NOT common to table A. I have 6 common fields. How do I go with this? I tried select … from Table A left join Table B, it missed some values from Table A. Tried also right join, full join…Any pointers on how to do
Tag: presto
count and sum in case statement
What is the difference below if I use case instead of sum? I believe I would get the same output? Thanks! Answer The three are equivalent. All of them count the number of rows that meet the particular condition (salary > 100000). All return 0/1 and would not return NULL values for the column. From a perfor…
Athena/Presto : Unnest 2 arrays with left join
So I have 2 Json arrays that need unnesting, and joining based on a key within the json structure. In theory is easy, but without having a ‘left join unnest’ functionality, it all becomes messy. I have achieved what I want, by grouping the results; but I also have concerns that it is doing 2 cross…
WHERE followed by ON clause
What is the semantic difference between the following queries? To me both seemed similar until I executed them on presto. Is this something specific to presto or am I missing something in the SQL standard? Form 1: All conditions specified in the ON clause. Form 2: Some conditions specified in the WHERE clause…
presto sql filter part of the string java
I have a string in col1 select col1 from table “blah blah 123 blah iterate 866 loss blah blah” how to filter string between iterate and loss and just show sum of all such filtered numbers ? Answer You can use regexp_extract to extract the number within that string, convert the values to a numeric …
Daily active counts of THING from rows detailing change of THING
Say I have a table of people who have used my service on a day N and a table describing what theme the users have changed to. There’s no table available telling me what theme they are currently using. …
Inner Join returned values
I have a query such as, select name from employee inner join task on employee.id = task.employee_id order by name asc Tables look like: employee id name 1 Emily 2 Sam 3 AI 4 Joe 5 …
Json extract coming as null – Presto SQL
I`m trying to use json extract to get some values using the code below: And it works, however the values are coming all as NULL . When I extract the properties field I get the values that I want, as the example below: {“allow_url”:false,”partner_target_app_id”:[28479748204829001,938801…
Presto SQL – Looking to explode Table of IDs to include predefined row numbers
Apologies if this title was a bit confusing. It’s tough to articulate this problem in a sentence. Here is my current table I’d like to explode this table to look like this: Any help would be much appreciated. Answer You want a cross join:
For rows with the same A, B, C values, how to select only “earliest” row (specified by column D) within a given time range?
I currently have a query (simplified below): Basically, the data source we’ve been getting data from writes their timestamps as when their job finished running rather than when the data actually came in (we can’t do anything about this on our end). Their jobs regularly end at 5-6 minutes after the…