i would really need your help in creating a query where i can show the number of buyers who made more than 1 order in a month for the period January 2017 to December 2017. Also, the no. of these buyers that didn’t order again within the next 12 months. Below is sample my data. Date CID (customer id) Ord…
SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets
I have table A which contains column time stored as timestamp datatype. Table A: Contains time column in HH:MM:SS in 24 hrs format. Answer Please use below code. Replace now() with time for your query. Explanation – firstly i am checking if hour is >12. If yes, deducting 12 to get the hour. Then sett…
Performing a Union on groups of rows without the same columns. Trying to generate empty histogram bins
I am using MySQL. I have a table grades, that looks like so: I have a view that counts how many grades are in each bin, for each class using the following query. This returns: I want to add empty bins to this view, like so: So the bins increment by 5, starting at 40, and stopping at 100. My
Query to display Employee and Manager
I have to write a SQL Server query to display the employee last name and his respective manager’s name, as given below. These are the schema for the two tables. Expected Output : I tried this code but its not right Detailed Schema: Schema Answer I guess something as simple as this should do it How it works: T…
verify flag for user as parameter
I have table users Userid flag 145 1 142 0 a second table Sales salesId date amount 252 01/01/2021 125 23 01/02/2021 300 I need to ckeck each time for user parameter …
Can I multiply the output of a SQL query from two separate tables within the same query?
I am taking two values (A, B) from similar but different tables. E.g. A is the count(*) of Table R, but B is a complex calculation based off a slightly adapted table (we can call it S). So I did this: …
MySQL – Enforcing update of row to ONLY be possible when a certain key is provided
This is something I can’t seem to find information on. Let’s say I have a table users, and for security purposes, I want any SQL query to only executable if a reference to the id columns is made. E.g. …
Count string occurances within a list column – Snowflake/SQL
I have a table with a column that contains a list of strings like below: EXAMPLE: STRING User_ID […] “[“”null”&…
Count number of students per one role SQL
I have a table students with student id, course id and role number looking like this (ordered by student_id): How to count number of students for each role? If student is enrolled in multiple courses with the same role number, then count this as one. The expected output for above table would be similar to thi…
Set minutes of timestamp to a specific value
What is the easiest way to set the exact minutes value of a timestamp? This only adds minutes instead of setting the exact value: Answer Use date_trunc() before you add 2 minutes: Or, to retain seconds and sub-seconds: Demo: Should be substantially faster than manipulating the text representation and casting …