I got a search sessions log that looks like this: My task is to partition each row into session groups. Session groups are up to five minutes. For example: Those TOP 3 sessions will form a group session 1 – if we accumulate the minutes between each row, we will get 3 minutes and the 4th would accumulate…
Tag: window-functions
Mysql subtract and sum
I have a below table in mysql. DROP TABLE IF EXISTS `mdc_data`; CREATE TABLE `mdc_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tariff_id` int(11) DEFAULT NULL, `msn` varchar(100) DEFAULT NULL, `…
Create Missing Data Hive SQL
I have a table that has an activity date of when things change such as 2020-08-13 123 Upgrade 2020-08-17 123 Downgrade 2020-08-21 123 Upgrade Basically this in relation to a line there are 3 …
SQL – get summary of differences vs previous month
I have a table similar to this one: It contains the stores that are active at BOM (beginning of month). How do I query it to get the amount of stores that are new that month – those that where not active the previous month? The output should be this: I now how to count the first time that each
Is it Possible to Merge Rows in SQL from Just One Column?
I wonder if it is possible to merge multiple rows from just one column in SQL. Here’s an example of my initial table: Here’s my expected output: I do not know if it’s possible to have a table like the later one. So, any advice would be much appreciated. Answer This type of task is usually do…
selecting max value from table with two variable colums (microsoft SQL)
i´m working with a table that looks like this: Start https://i.stack.imgur.com/uibc3.png My desired result would look like this: Result https://i.stack.imgur.com/v0sic.png So i´m triyng to select the max value from two “combined” colums. If the values are the same amount (Part C), the outcome does…
How to capture first row in a grouping and subsequent rows that are each a minimum of 15 days apart?
Assume a given insurance will only pay for the same patient visiting the same doctor once in 15 days. If the patient comes once, twice, or twenty times within those 15 days to the doctor, the doctor …
SQL PIVOT-WINDOW FUNCTIONS
With the sql query I get table 1 table 1 Trying to pivot using window functions below on Server 2012 fails My desired output Answer Use conditional aggregation Also, you can try with pivot –
SQL Query calculating two additional columns
I have a table which gets populated daily with database size. I need to modify the query where I can calculate daily growth and weekly growth. Current Output I would like to add two additional columns which would be Daily Growth (DB_SIZE sysdate – DB_SIZE (sysdate -1)) Weekly Growth (DB_SIZE sysdate …
Auto increment temporary column in select statement MySQL 8
Hello i use this type of sentence to return an auto increment column in my prepared statement selects cnt := cnt + 1 SET @query = CONCAT(‘SELECT * FROM (SELECT (@cnt := @cnt + 1) AS id, a….