I have a user’s table that has info about user_id, places user purchased tickets, and the time user had purchased the ticket. Users: I want to add a new column that shows the user’s most visited place at the time of purchasing ticket. The table would like (Snowflake): Answer You want to use a WINDOW version of COUNT to get
Tag: snowflake-cloud-data-platform
Finding shortest geo-spatial distance from one point to all other points in SQL
There are two types of users who purchase the movie tickets from either town A, town B, town C or online. I have the following tables as: Locations: This table consists of locations of movie centers Users: This table contains the history of user’s purchase i.e. either online or in towns. Also consists of user’s latitude/longitude during the purchase. I
Conversion of time zone in snowflake sql
I am doing a conversion of timezone from current_timestamp to ‘US/Eastern’ in snowflake view. I was able to create the view but when I preview the data I am getting Timestamp ‘US/Eastern’ is not recognized error. I am converting like below: ((CONVERT_TIMEZONE(current_timestamp, ‘US/Eastern’) – INTERVAL ‘5 HOUR’)) Answer Thus looking at TIMEZONE doc’s LukStorms points, and following the link to
Change Dateformat in a Column from dd.mm.yyyy to yyyy-mm-dd in SQL
I have a column with Dates like ‘12.05.2021’ and I want to transform the Dates to following format: 2021-05-12. But I dont know how to do it. Answer i assume it’s in string ,so :
How to update a date column based on proceeding order?
I have a table that contains three different status’ CLICKED > CLAIMED > BOUGHT (this is the correct order) (see table below for example) For each number_id, I need each of these status datetime to be in a separate COLUMN. In the last query, I need the query to have the LAST status based on the updated_at_datetime, and each of
How to grab certain value found in a string column?
I have a column that contains several different values. This is not in JSON format. It is a string that is separated into different sections. I need to grab everything that is found under ID only. In the examples below, I only want to grab the word: “syntax” and “village” The above does not work since this is not a
Using REGEXP_SUBSTR to extract website domain
I have a field called Website with examples that look like: I am trying to use REGEXP_SUBSTR to isolate the domain: REGEXP_SUBSTR(“Website”, ‘[^https://]+’) Some of the results are working but others are not, for instance I am expecting cornstalk.com and penny.co but I am not receiving those values: Any help would be appreciated. Answer You can use Details: ^ –
Removing rows based on a string in Snowflake (SQL)
Let’s say I had a table as follows: And what I want to do here is if an ID has a group and the same ID has the group-Direct, then the Direct row is removed. So what It should look like is: What happened? The only ones to change were ID = 001 and ID = 005. ID = 001
Optimizing SQL query – finding a group with in a group
I have a working query and looking for ideas to optimize it. Query explanation: Within each ID group (visitor_id), look for row where c_id != 0. From that row, show all consecutive rows within that ID group. Answer so you have a common sub expression so that can be moved to a CTE and run just once. like but the
Create new column with all unique values from another column in SQL
I have the following table: I want tto create new table which will show the unique crop names, count of the crops appearence and then list of all the growers that grow this crop,so the result table should look like this: I manage to create table that shows the crops and the total count without the growers names: My question