Let’s say your web application tracks the activities of its users using a tracking system. While a user hasn’t logged in or signed up, all the user’s actions are tracked using anonymous_id and the user_id is null, and afterward, they are tracked using the same anonymous_id and user_id. It is…
Tag: sql
Partial Group Filter
Oracle 11g How can I get ALL pet_owners of FURRRY and SCALY and only earthworms and slugs ? Answer You want either of the two conditions to be met, so you need OR:
Check if a pair of records belong to multiple group IDs
I have a table that contains 2 IDs – UserID and GroupID. I need to pull a list of all UserIDs that “share” the same GroupID at least 4 times So, based on the following data set: I’m trying to generate the following result: UserID A UserID B NumberOfOccurrences 1 2 4 2 3 4 1 3 5 I’…
Finding Active Clients By Date
I’m having trouble writing a recursive function that would count the number of active clients on any given day. Say I have a table like this: Client Start Date End Date 1 1-Jan-22 2 1-Jan-22 3-Jan-22 3 3-Jan-22 4 4-Jan-22 5-Jan-22 5 4-Jan-22 6-Jan-22 6 7-Jan-22 9-Jan-22 I want to return a table that wou…
how to single out certain names in sql
So I have this problem in SQL where the question is this: List the sId and name of students that applied to “WSU” But not “U of O”. and my attempt was this: However this does not do the trick as it doesn’t catch that some of the students had already applied to U of O. MRE: And then of
How to add all text in a column into one cell in Snowflake
So I have data as follows: All I would like to do is make a new table which gathers all text into one cell as follows: So all values of tags are in one cell for each ID. Order does not matter, but they must be separated by commas. Answer how about this :
Invalid group by expression error when using any_value with max and window function in Snowflake
I was given a query and I am attempting to modify it in order to get the most recent version of each COMP_ID. The original query: I then attempted to use a window function to grab only the highest version for each comp_id. This is the modified query: When attempting to compile the below error is given: SQL co…
Calculate number of times a user visited a page in SQL
I have table as shown below. It has 3 columns user, page and timestamp. In the above table I want to calculate number of times a user visits a particular page. Each record in the above table is a hit, so just grouping by column page wont help. I essentially want to count continuous records for a particular pa…
When do Postgres transactions fail?
I was wondering at what point a transaction actually fails in Postgres. By this I mean stop working, and return an error. Is it after the first failed query? Does it try all queries and then return failure? Hopefully it fails fast on first failed query, but I don’t know for sure. For instance, if I have a tra…
How to grab the ID of an artist so i can insert the album into the database with the artist’s ID?
Here is my schema: This is what I’m doing: Right now I’m inserting into albums by looking at the table for the artist ID and typing manually. How can I do this automatically? My application will receive the song title, artist name and album name. So it will need to look for the artist ID if the ar…