I have a working query and looking for ideas to optimize it. Query explanation: Within each ID group (visitor_id), look for row where c_id != 0. From that row, show all consecutive rows within that ID group. Answer so you have a common sub expression so that can be moved to a CTE and run just once. like but t…
Get date today, if no data – get data from yesterday (dynamic)
I have such an issue. I have a report that is built around today’s date. We see data only from today. On Hand units from today. However, we have many import issues and sometimes import fails and there is no data in this report. I was wondering if there may be a way to make it dynamic like to use
Include zero counts for grouping date ranged based SQL query
I’m trying to group and order the number of sales made in each day from a single ‘sales’ table with a created_at column and an id column. Each of the records might be created through out the day at any time. I’ve managed to do this with the following query: However, the days with 0 sal…
Select only those columns from a table header which are present in data dictionary ColumnName
Prerequisite : all the tables are dynamic so i cant use the column names I have two tables Candidate table : Table which has all columns and data required to be selected DataDictionary : Table where i have only those columns which are to be selected for querying Now what i want to do is select only that data …
Quartiles in Postgres
I have payment amounts like so – How do I divide records like this into quartiles. It doesn’t have to be perfect. For example, the data in the question would transform like so – Ids 124049 and 2323 are given the same quartile because those payment values are close together. Answer you can us…
Show when order number is unique for the item number
I attached a simple table with two columns, Item number and Order number. In the example attached only item number C5664 and A9930 met the criteria. How do I write this code in SQL? Answer If you group by the item numbers then the unique count for the order numbers will be 1
SQL – Consolidate and group records from multiple rows to columns
Is there a way to transpose multiple rows and group them to columns? I have a table that has data from Invoice values from different tables and columns. Now, I want to have them by Invoice numbers. InvoiceNumber Table Column Value 123 A Column A1 ABC 123 A Column A2 DEF 123 A Column A3 GHI 123 B Column B1
Oracle procedure saving SYSDATE
As part of a larger procedure I’m trying to write some code, which saves SYSDATE into a variable but I’m running into a problem. I narrowed it down to the following. Can someone please help me out. Answer In PLSQL you can just assign: if you insist on sql all you have to do is to add into after se…
How to sum up and other calculations by the other columns in the same table?
id amount 1 20 2 20 3 20 4 30 5 100 6 20 7 30 8 100 I would like to create three more columns which will be calculated by the amount column. Expected output: id amount openamt debamt credamt closeamt 1 20 340 140 200 400 2 20 340 140 200 400 3 20 340 140 200 400
Count ROW type item Athena / Presto
I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date