I create a table using the command below: CREATE TABLE IF NOT EXISTS stats ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, session_kind INTEGER NOT NULL, ts TIMESTAMP NOT NULL DEFAULT …
Tag: group-by
How to overlap NULL values in MYSQL when using group by?
This is my current table, let’s call it “TABLE” I want end result to be: I tried this query: but it doesn’t work i tried replacing NULL with 0 and then perform group by but “TBA” (text value) is creating problem, kindly help me out! Answer This looks like simple aggregation: This takes advantage of the fact that aggregate functions
How can I efficiently query for all rows in a database matching a filter based on a joined table?
Apologies if the question title is unclear; I’m not sure how to phrase this without more detail. I have a table foos like: id (bigint) | details (json) 1 | {someKey: ‘someValue’ …} 2 …
Why is my calculating a percentage not working correctly when I am dividing?
I am trying to see what percentage of customers paying LL and the percentage of customers that are paying LP. For example I have 1 customer in LL and 1 in LP so percentage should be 50% in Cust_LL_Pct …
SQL – get MIN value of row and check this MIN value to be in row at least 2 times
What I’m trying to achieve is this: 1) Get the minimum value of a salary in the table for every department. 2) If this minimum value exists in the table at least two times for every department, then …
Last Row In Group Based on Timestamp Column For a Subset of Group Ids – Postgres
Given a postgres table of the following form: group id | timestamp | value ———+——————————-+—— 1 | 2020-04-15 15:04:44.020288+00 | 8.0 2 …
SQL ON SINGLE TABLE GROUP BY LIMIT 10
I have a table with items (id_item, name, category, stock,…) I’d like make a query to group by result on category and LIMIT 10 first items which are in this category Is it possible? Answer You can use row_number(): The ? is for the column that specifies what YOU mean by “first”.
Syntax for counting greater than and summing a value with multiple joins without the keyword having
I’m learning SQL (Postgres) and I’m slightly confused about something I’m trying to do. I have the following tables. Employee: Project: Department: And works_on: For this, I was trying to do something slightly more complex: To create a view that has project name, department name, number of employees, and total hours worked on each project that has the criteria of
Transpose Rows to Columns Dynamically in MySQL – Page Rank Per User
I have the following data frame. I want to create a new table with the 4 most viewed pages per user (page and number of times viewed). So, I need to transpose the following columns: page, quantity and rank. Note that the variable to order is the rank and not necessarily all the names were in all the pages. Necessarily
SQL how can we get monthly trend from 2 separated columns start_date and end_date?
Imagine we have the following data: The question here is to get the 2020 monthly trend of active paid subscriptions. For each subscriber (ID), we can only count the months that they are active. So for S1, we can only count S1 active in Jan 2020 and March 2020, not Feb 2020. During the interview, I wrote a function and