Categories
Mastering Development

how to take only last transaction to count timediff mysql 5.7

this is the continue question from find out time difference for every user in condition mysql 5.7 this is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=31b3be9d1e2444eb0b32c262176aa4b4 i have this table CREATE TABLE test ( ID INT, user_id INT, createdAt DATE, status_id INT ); INSERT INTO test VALUES (1, 13, ‘2020-01-01’, 8), (2, 13, ‘2020-01-03’, 8), (3, 13, ‘2020-01-06’, 8), […]

Categories
Database Development

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), content TEXT, icon VARCHAR(50) ); resources contains all resources under each category create table if not exists resources ( resource_id INT, title VARCHAR(255), content TEXT, link VARCHAR(1000), category_id INT […]

Categories
Mastering Development

SQL Least Earning Location Count

I am using an online editor to solve query https://www.jdoodle.com/execute-sql-online/ have the following SQL Table create table CITIES(id int, name String); create table USERS(id int, city_id String, name String, email); create table RIDES(id int, user_id int, distance int, fare int); INSERT INTO CITIES (id, name) VALUES (1,”Cooktown”), (2,”South Suzanne”); INSERT INTO USERS (id, city_id, name, […]

Categories
Development

Fetch data from last week, even if no entry is present on few day

I want to fetch data from two tables post, post_like I want the data such that if row entry is not present in post_like table null/0 as result to be shown currently row data is omitted if the data is not present for a particular day. I have prepared sample data CREATE TABLE post(post_id INT, […]