Given this table. I would like to know for each day how many different customers made a sale on date t and and t+1. The result for date 2021-06-30 is 2 because customer 1 and 3 made a sale in t and t+1. Answer Use LEAD() window function for each distinct combination of date and customer to create a flag
Tag: sql
Remove duplicated rows with same Timestamp but different values
I have “duplicated” rows in Bigquery and I need to keep just the last occurrence grouped by id of element. As you can see, these are not duplicated rows, those are duplicated Timestamps with different values. I need to keep one registry per Timestamp. I run this query to get the example: Table wit…
How to process json data and put it into arrays in postgresql
I have a postgresql table with two columns. name VARCHAR(255) and notes JSON. A sample dataset might look like this: Now I want to do two things. put the list from the json at ‘link_to’ into another column called referrals_to (which must be of array VARCHAR type then). From my example: name notes …
How do I do nested if statements in SQL?
Write a program that will check the answers for the latest test. The program will be given a table answers with the following columns: id – the unique ID of the question; correct_answer – the correct answer to the question, given as a string; given_answer – the answer given to the question, …
Turn these temp tables into one longer subquery (can’t use Temp tables in Power BI)
Currently I have created these temp tables to get the desired output I need. However, Power BI doesn’t allow the use of temp tables so I need to get this all into 1 query using inner selects. This code is simple and straight forward. However I can’t seem to figure out using a subquery. Here is wha…
Update of many rows with join extremely slow
I have a table with five relevant fields – id, source, iid, track_hash, alias. I want to group all entries into groups with a common track_hash and then for each row save the id of the row with the lowest source (with ties broken in favor of the highest iid) entry from its group into the alias field. To…
Convert values in related table to comma-separated list
I have two SQL Server tables: I’m trying to the following result where the related activities are in a comma-separated list: I tried: Can someone help me with this SQL query? Thank you. Answer You could use OUTER APPLY to aggregate the string if you’re using SQL Server 2017 or higher.
Compare two tables via the three tables SQL
I plan to compare two tables via the three table. my query is as the following if the count is zore, then the tableA and TableC match, otherwise, these two tables do not match It takes a long time to run the query. Do we have a way to compare tableA and tableC fast? Question: How to compare tableA and
Converting string to a date but date not formatted properly
I am having issues with converting a string into a date. The issue is that the format of the string does not easily convert to a date. For example, all the dates are set up like APR-19, or JAN-21. I am not sure how to properly cast this. Here is the SQL I am using. Answer in PostgreSQL it is
Getting values from 3 tables where two of those are self-joined
Problem Introduction: I’m working on a faker e-banking system as a side project for practicing my SQL skills (which are super bad), and I’m now stuck at a point where I want to pull data from two tables but cannot do that with joins So, basically I have a users table which looks like this: And I h…