postgresql raffle draw query
goal draw first place to fifth place tickets. a user can't win multiple places. If a user hasn't placed, then all of their tickets must be considered in the draw. progress I can draw one winner, but not all five.…
Extract first N values from a json field in PostgreSQL
I have a table in postgressql with column that holds an json object: create table test ( rec_id int generated by default as identity, usr_id int unique, format text[], syn_data jsonb, generation_date timestamptz); insert into test values ( 2, 144,…
how i can optimize performance of this mysql query?
I want to optimize performance of this MySQL query SELECT * FROM ( (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%leo%') UNION (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%le%') UNION (SELECT * FROM users USE…
Asp.net – Is this good practice and does it make sense for CRUD operations?
This is my first CRUD app, and I am trying to determine the best way to implement a clean architecture to access the more than 20 tables I currently have in my database. I am using Dapper as I want…
Postgresql – SQL Split a table into buckets of equal size, while preserving groups
I have a table which has id and group_id foreign key. I want to create (N=2) buckets of equal size (approximately), such that all rows with given group_id end up in the same bucket. For example, given: id, group_id 1,1…
Postgresql – Can DBT source identifier names begin with integers?
I'm trying to select from a snowflake source as shown below called "s_2020_09_history_logs" extract from staging model: with unioned_archived_history_logs as ( select * from {{ source('emspdb_archive', s_2020_09_history_logs) }} extract from _sources.yml: - name: emspdb_archive database: lake schema: emspdb_archiveschema tables: -…
PostgreSQL: View adjust Date based on hour
I have a view that queries sales data into a condensed table which sums net_sales for each location. The problem i have is when there are sales after midnight. My code is grouping by Date so any sales after midnight…
Mysql – Perform Same Query For Every Id in List
I have a table called scores. id is the auto-incremented primary key. id player_id score 1 1 100 2 1 57 3 2 434 4 3 11 5 3 132 6 4 32 7 4 100 8 4 21 Given…
Mysql – Order table based on 3 attributes
Hi I have the following table, FILE_NAME MODIFIED_DATE PRIORITY A 2022-10-11 1 A 2022-10-03 1 A 2022-10-02 1 B 2022-10-11 3 B 2022-10-01 3 C 2022-10-11 2 D 2022-10-11 4 I would like to order the table based on MODIFIED_DATE,…
Mysql – SQL: CAST combined with window function causes syntax error
I want to condense my database, which now contains a large number of values, to 1/12 of the original size by averaging twelve consecutive values. To do this, I form the mean value using the types TIMESTAMP and FLOAT(4,2). In…