Skip to content

Tag: sql

SQLite select query if inner join query doesn’t exists

I have two tables, one for all the foods which contains food id, default name and other foods values. Another table is for the food name translations which contains food id, language id, translation. What i want to do is join between these tables by the food id to get the translation for food id = 5 for examp…

Writing a Single Query w/ Multiple CTE Subqueries SQL/R

I have some data I would like to pull from a database, I’m using RStudio for my query. What I intend to do is write: The first CTE statement to pull all my necessary information. The second CTE statement will add two new columns for two row numbers, which are partitioned by different groups. Two additio…

Flag the date when they return

Story: For each id , they have a join date to a subscription and when they get rebilled monthly, they have a returning date. The first part of the exercise was to flag consecutive months of returned dates from the join date. Here’s an example: Objective: What I would like to add is to flag those who ret…

MySQL select complete last month

How to select all data from last month (or 30 days)? I already found some answers, and mostly gives this solution But this gives me also the dates from the future I am only interested in the days from last month or 30 days (not next month and beyond) Answer Is this what you want? I added a condition so

max, over partition by, oracle, take max rownum record

I am trying to pull the maximum rownum in a partition. I am getting the below error message, so I need help to fix my SQL Query. I added in a row number and a row number in a partition in my SQL query. Code is below. I want to take the maximum over this partition and have tried changing