Skip to content
Advertisement

Tag: presto

SQL statement to query new buyers on rolling basis

I currently have a order table that looks like this: I have been trying to create an SQL statement that will return something like this by doing a count(distinct user_id) : Of course, there will be multiple item_ids in the order table. What I’m trying to achieve is to obtain the rolling number of buyers that have never bought that

SQL: Efficient way to count and group results by like value

I have a table that looks like this: What is the most efficient way to query it and return the following ? I was thinking to use case when statements but it seems messy. Answer In Presto you can split the delimited list into an array, then unnest the array. This gives you one record per element in each list.

Presto how to find start date given week

I want to find start date from given ISO week (which can range from 1-53, Monday as starting day) and year using Presto SQL query. i.e. year – 2020 and week – 2 should return 06/01/2020 Is there any inbuilt function for this ? Table structure: Answer There’s no direct way for constructing a date from a year + week

Athena geospatial SQL joins never complete

A very basic geospatial join, based on this example, times out every time. The table polygons contains 340K polygons, while points contains 5K rows with latitude/longitude pairs (and an ID). Both are single .csv files in S3. Query: The SQL query above never completes in the default 30-minute Athena query time limit. I’ve found vanilla Athena queries on large-ish data

How to convert list of comma separated Ids into their name?

I have a table that contains: I have the table that has the names of this tasks: I want to generate the following output I know this structure isn’t ideal but this is legacy table which I will not change now. Is there easy way to get the output ? I’m using Presto but I think this can be solved

Advertisement