I’m dealing with an old database that contains records of 365 days of the past 3 years. I’m trying to get the 2016 records ‘for example’ and change the year to 2020 and then add my new record to the table so it would contain now records of 4 years. this is what I did so far : I want
ORDER BY num-rows of another table
I have to create a ranking of the directors who have made the most reviews. What is the method for reordering according to the number of rows in another table? Table 1 (director) Table 2 (employee_reviews) Result (Directors ranking): Answer You need to join to your table and select the count when (ON) the aut…
How to count monthly retention user in bigquery?
I have raw data as below. With each line is the record of an transaction of user, and the month when they made the transaction What I want is to calculate the number of user who made order in a month and the number of repeated user (RETENTION) from last month, then I can know how many % of user
how to join with IIF function?
Hi I am using MSSQL 2017 and try to figure out how this code works DECLARE @customer TABLE ( credithold bit, id int primary key, deliverycityID int, postalcityID int ) DECLARE @city …
Redshift – Missing latest date when join two tables
I have two tables (Calling as A and B table); Table – A data only includes the last 1 month data. Table – B data stores all data that you have. I need to find the difference between these two tables dates, but when I left joined the two tables I have null dates: I am going to use last_value
Subqueries on same table
What I want to do is run a subquery from the same table in a single query my SQL is below: SELECT DATEPART(DAY, dateTimeCreated) AS DateClicked, COUNT(*) AS NumClicks FROM […
Find the count and indexes of occurrences of a string
Suppose given a string, how can I get the character, no. of occurrences and occurrence indexes in a single query? Eg : Consultant Character no of occurrences index: C 1 1 O 1 2 N 2 3,…
Recursive CTE in Postgres
I have a table with the structure: And, for each employee, I need to show the top manager ID. I mean, if for example, I have the EmployeeID 2 whose manager is 3 and, therefore, the number 3 has the manager number 5, I would have to show: I need to do this with a Recursive CTE in Postgres. Answer
BigQuery Standard SQL SELECT rows WHERE field contains words from another table field
I have 2 tables in BigQuery. VIDEOS table contains video names and tags. CREATORS table contains information about video creators. The VIDEOS.tags field contains comma separated quoted strings. I need to select all the videos that were tagged with names in the CREATORS table with results looks like this: But …
Querying an SQL table to get all new values in a column according to filter in another column
Sorry I couldn’t make the question clearer, let me explain it here. I have a table that has two columns: What I want to do is the following: check all new IDs that appeared in 2014 from 2013, and all new IDs that appeared in 2015 from 2014, and so on and so forth… So, from the example above, the