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…
Tag: sql
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
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
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
How to enforce that there is only one `true` value in a column per names (in an enum) of another column?
I have the following structure with an enum { ‘ready’, ‘set’, ‘go’} on name: How can I put a constraint on it so that there will only ever be 3 true’s (one on ready, one on set, and one on go)? Answer You can use filtered unique indexes (or as Postgres calls them R…
MySQL query to find values in table with given value and find others that are similar
I have a table called followers and I want to be able to find the current users followers, display that list, and then compare those values against another user ID to see if they are following them or not from that same table This is the table I’m using currently to get the list of followers for a speci…
T-SQL sub select to retrieve rows with columns that have values instead of NULLs
This example is as close as I can demonstrate for the problem I’m trying to solve. For this case the sub-select has some mostly duplicated rows with the only difference being that some have a delvieryDate and some do NOT. If a row of data is available with the date, I want that. If not I’ll need t…
Dynamic pivot unknown number of rows – Sql View
I’m using SQL to create a database VIEW from the table below. ItemCode |BcdName |BcdCode | ————————|——————|————-| 03616006.ERA …