I am trying to create a common data field based on multiple columns below. When I try to use nvl i am getting blank values in the common field and its not getting picked up..The input and output tables are below: Input table model-manufacture model -CAM MODEL -schedule CX190 NM890 NM890 JK1231 LO121 LO121 VB1…
Only return rows where Status Online is the only status is applicable (perhaps a coalesce function or qualify function)
ID Status Date 1 Online 2022-06-31 1 Offline 2022-07-28 2 Online 2022-08-01 3 Online 2022-07-03 3 Stationary 2022-07-05 4 Offline 2022-05-02 5 Online 2022-04-04 5 Online 2022-04-06 The output I’m looking for in SQL/BigQuery ID Status Date 2 Online 2022-08-01 5 Online 2022-04-04 5 Online 2022-04-06 So wh…
How to calculate total balance in sql using sum?
updated question — I have a table that contains the following columns: I need to calculate the balance based on the update date. In some cases there can be the same update_date for two different records. When I have this, I want to grab the lower value of the balance. This is the query I have so far: In…
Foreign key relationship with boolean values
I’m working on a feature. I’ve three different car types (Sedan, Hatchback, SUV): I’ve 6 features in total. Out of 6, 4 features are their in every car. The second car category has 5 and the third has all the 6 features. Here where I’m getting stuck: I’ve to send all the 6 featur…
Complex Conditional Query in Where Clause
In my company, each department has their own statuses for purchase orders. I’m trying to load only the pertinent POs for a specific user’s department. I keep getting errors. It seems like it should be correct, though. I initially attempted to use a Case statement, but it appears that SQL can only …
Google Bigquery SQL with clause `where 1=0` is not returning correct schema
I recently added a new column in my BigQuery Table. The following code snippet is used in legacy code to determine the table schema But the problem is that it is not returning the newly added column in the df. Although when I use some other condition like 1=3 in where clause or limit 0 then it returns the cor…
How to get data for administrative weeks of a month?
What I want to do is to get the data of each “administrative” week (Monday to Sunday), that is if a month starts a Wednesday,the next week when I would launch the query, I would want the data from Wednesday, Thursday, Friday, Saturday, Sunday of the past week. This would get me data of week 1, and…
Insert only specific columns with DBT
As of now, I have an INSERT INTO clause, that works like this: The final_table has 4 columns: ID (incremental/serial), name, age and created_at. The person_table has only name, age, and created_at. When I translate it to the dbt logic, it looks like this: However, dbt keeps compiling as an INSERT CLAUSE conta…
Get JSON object keys as array in Presto/Trino
I have JSON data like this in one of my columns and I would like to run a query that returns the keys as an array Answer Convert your JSON into a MAP and then use map_keys(): Use json_parse() if your JSON column is of type VARCHAR Output: _col0 bar, foo foo
how can i optimize this mysql statement, it is very slow
Answer This query is slow because it is actually 16 x 3 = 48 queries. You have 16 SELECT correlated subqueries in your main SELECT query, and you’re looking up three values of p._id. Your first step is to transform at least some of those subqueries into independent subqueries and JOIN them. (This is a v…