I want to get unique customer counts. I have reference of postgresql query. Could you please convert this query into HiveSql Answer Use case expressions: One more method for counting distinct is size(collect_set()):
Tag: sql
How to use over – partition by query in SQL in order to get the current, average, and maximum value?
I have this table where it shows a point that was being done by a device at an area and specific location. I want to get the current, average and also maximum points grouped by area and also location. If I choose any day, the current qty will show the quantity of the latest working date. Meanwhile, average qu…
Deleting rows in sql rows based on pair value
I have SQL test table as below: Item Doc_No Code Line_Item 1 abc1234 101 01 2 abc1234 102 01 3 def5678 101 01 4 def5678 102 01 5 ghi1234 101 01 6 ghi1234 101 02 7 jkl5678 101 01 I am trying to eliminate rows when duplicate values of “Doc_No” has pair values of “101” and “102̶…
Convert Postgresql into HiveSql
How do I convert below mentioned postgresql query into HiveSql Answer Use CASE expressions like this: Also in Hive version >= 1.3 you can use quarter function:
How do I restrict my SQL aggregation Rollups to a specific grouping?
The following SQL returns 5 grouped columns and two aggregated columns: I do want the rollup to the level of TenancyName, but it don’t need all the other intermediate rollup lines. In fact, you can see that rolling up from the Doctor’s (Employee’s) row up to the EmployeeId produces the exact…
How to have MySQL return multiple column of values based on a different benchmark?
I have a MySQL database table (sales) consisting of sales data with their dates. I have a SQL query that relies on a specific date benchmark. This query supposedly returns only one numeric value. Now I set the condition to be dependant on the benchmark. The problem is, I don’t want this benchmark to sta…
how to select third, fourth, fifth date in sql using window function
The table rental of movies has this data: how to generate pairs of : first rental_ts, second rental date, second rental, third rental date, …. second LAST rental date, last rental date, last rental date, null? using window functions- this is not working: i expect: basically i need more than 1 level back…
How do I perform a dynamic WHERE clause in QuestDB?
I’m looking to create a dynamic WHERE clause in QuestDB for something like the following example SQL: What’s the nicest way to do this? I can’t find guidance in the WHERE keyword documentation Answer This depends on what you’re trying to achieve, but what might be useful is using coale…
Custom Week Number SQL
I need to convert the following logic to python and SQL (SQL query is more imp): I have a table with ID and Date columns. I need to add a column called “Week_Num” such that: Every time it sees a new ID, Week_Num becomes 1 7 dates correspond to 1 week so if the first week begins on 29th Oct
how to create schema in mysql and how to import existing record into the database?
I have file with name data that contain information like in my linux machine I have created a database in Mysql with name “invaliduser” and inside that i have create table “invalidlog” and i created a schema like this when i am trying to import the data file in mysql with the following…