I have a set of SQLITE tables that adds up to 7 GB on disk. The particular table I am querying (FQ) has approximately 50 columns, and 3 million rows. I am querying from the sqlite3 interactive shell (…
Tag: sqlite
How to get the most recent data from a table?
Here is the table that I have The question that I have for my query is “Using the most recent month of data available, what is the range of estimated home values across the nation?” Since my table is small, I know the recent date is 2020-06. So I wrote this code: And the result that I get is: Anot…
How to get the row index in SQLite3?
I have created a table that has a column of registration number as primary key. I have created row_id using row_number() that is ordered by the primary key. How can i search a registration number and get the row_id along with other information of that row? Answer If you have created the column row_id like: th…
How to pull a list of all visitor_ids that generated more than $500 combined in their first two sessions in the month of January 2020?
Tables: Sessions session_ts visitor_id vertical session_id Transactions session_ts session_id rev_bucket revenue Currently have the following query (using SQLite): I’m having trouble identifying the first two sessions that combine for $500. Open to any feedback and simplifying of query. Thanks! Answer Y…
Create a database in python where user inputs the data?
I would like the program to ask the user for information (i.e. age and name) and for this information to be put into the database. I have tried the following: The error that keeps appearing is: I have Python 3.7 and I work in spyder. Answer Try this, student_name is a string and should be enclosed in single q…
Updating Multiple Columns in sqlite3
I am wanting to update multiple columns in my ‘Class’ Table in sqlite. I do not know how to do this so I approached the same syntax as an ‘INSERT’ query however, I seem to be having the following syntax error: sqlite3.OperationalError: near “VALUES”: syntax error The line I…
Optimize SQLite Query using GROUP BY
I am trying to optimize my query which uses GROUP BY. But can’t it takes too long time. I have a virtual table which contains 3 columns: I want the group to be just as the query below, but it just takes a bit too long to run. For example I tried this first which works pretty good but still
Row sorting and selection logic in Python on Sqlite db
hello Thanks for taking the time to go through my question. I work in the budget space for a small city and during these precarious time, I am learning some python for maybe in the future helping me with some financial data modelling. We use SAP currently but i also wanted to learn a new language. I need some…
How to make IN on values from cell?
I have table which contains list of data ceparated by coma eg I am trying to left joint it with IN clause, but without success Answer One option uses string functions:
How can I calculate percentages from total rows and status in sql?
I have a table that contains a list of templates and statuses that I am trying to calculate percentages on. My schema looks like the following: SQL SQL to Create DB Sample Data: What I’m trying to achieve: The query I have so far is the following: The problem I have is that my percentages return nothing…