I have cumulative counts for two groups over time in this format: Date Group Cumulative Count 1/1/2020 A 1 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 B 2 And I’d like to reshape this data into this format: Date Group Cumulative Count 1/1/2020 A 1 1/1/2020 B 0 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 A 3 1/3/2020 B 2
Tag: metabase
Extract JSON content in Metabase SQL query
Using: Django==2.2.24, Python=3.6, PostgreSQL is underlying DB Working with Django ORM, I can easily make all sort of queries, but I started using Metabase, and my SQL might be a bit rusty. The problem: I am trying to get a count of the items in a list, under a key in a dictionary, stored as a JSONField: Exam…
SQL subtracting 2 subqueries
Fairly new to SQL and I wanted to see if I could get some help with subtraction. I want to subtract studentstaken – studentsnotreviewed and then as assign the alias total to the operation. I was thinking something along the lines of But getting some syntax problems I’m not too sure about. [Sample …
Getting sum of an alias
Trying to take the sum of the alias studentsnotreviewed, what is the best way to approach this I’ve been stuck trying different things. I want to get the total number for the alias of studentsnotreviewed. [Sample data] School examID examstart examend studentsnotreviewed duedate sum 343 458092 Mar 16, 20…
SQL subquery to find overlapping customers
I’m working on writing a SQL query that would allow me to find customers who bought a certain variety of a product during specific sale dates and then subsequently bought a different product within a different date range. I want to use a subquery in the from clause to search for the second group of cust…
Calculate Time spent by users on the platform
I’m trying to calculate the time spent by users on my platform on a weekly basis. I have the following api_request_logs table (*Note: each row is duplicate) I tried this query, however, it doesn’t seems it is outputting correct calculation . Current output: Desired output : What I’m doing wr…
Unable to join queries
I hope you’re able to help with this, it is creating a lot of unnecessary work for me… I have a lot of users that are assigned a unique session_id each time they start the game. This means they can get multiple session_id ‘s in a single day if they crash and/or just restart the app. I want t…
SQL: Running Total of Occurrences
I am using Metabase with a PostgreSQL implementation. I’d like to achieve the outcome in the photo. I wish to include a column that counts how many times a field has occurred above. It should include the current instance as well. Other examples I’ve seen have simply counted total occurrences. As I…