mariadb – Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so:

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

What I want to do is find the total points for each person (student_no), and show 5 of these rows in a table, with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order – with highest at the top). This will be like a score board but only showing the user’s total points (over all levels) with the two above and two below. So because points could be equal, the timestamp column will also need to be used – so if two scores are equal, then the first person to get the score is shown above the other person.

I have tried this below but it is not outputting what I need.

SELECT 
    student_no, SUM(points)
FROM
    (
    (SELECT 
        student_no, SUM(points), 1 orderby
    FROM student_points a
    HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123
    ORDER BY SUM(points) DESC LIMIT 3)
    ) t1
ORDER BY orderby ASC , SUM(points) DESC

This is a dbfiddle of what I am trying:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

bitcoin core – How to secure a blockchain based on PoW against 51% attack?

I couldn’t find a satisfying answer to this 51% attack issue, so for a new blockchain with only 300 mined blocks, from my understanding the attacker has to rebuild all the blocks from scratch, is that true, if yes then what if the blockchain has 100k or 300k blocks? is there a way to prevent or penalize a miner if he mines too fast? does having honest miners would solve the issue? what about multiple Full nodesI need practical solutions

c# – Replace string based on multiple rules, don’t replace same section twice

Given a 2-column CSV dictionary file that contains translation of certain words, this c# code needs to replace the first occurrence of a word in the dictionary.

  • Once a segment of string has been replaced, it cannot be matched or overwritten by a new dictionary word.

This sounded like a fairly simple task but the code I came up with runs horribly slow and I’m looking for ways to improve it.

All the offending code is in the TestReplace function, I build up a hashset that keeps track of what character Ids in the string have been touched. When you apply a rule that changes the lenght of the string, all the character ids switch around so they have to be recalculated and I believe it costs a lot of performance. Wish there was a simpler way to do this !

Here is a super simple case of what the code tries to do :

Dictionary file:

hello >>> hi

hi >>> goodbye

Input: hello, world!

First rule is applied so string becomes -> hi, world. The word hi is now locked.

Second rule is applied but the string does not become goodbye, world since this part is locked.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace stringReplacer
{
    class Program
    {
        public static Dictionary<string, string> ReplacementRules = new Dictionary<string, string>()
        {
            {"John","Freddy" },
            {"John walks","Freddy runs" },
            {"brown dog","gray dog" },
            {"dog","cat" },
            {"- not -", "(not)" },
            {"(","" },
            {")","" },
            {"whenever",  "sometimes, when"},
            {"raining", "snowing" },
            {"his", "many" }
        };

        static void Main(string() args)
        {
            string Input = "John walks his brown dog whenever it's - not - raining";
            string ExpectedOutput = "Freddy walks many gray dog sometimes, when it's (not) snowing";

            string TestReplaceOutput = TestReplace(Input, ReplacementRules);
            ValidateReplacement("TestReplace", TestReplaceOutput, ExpectedOutput);
        }
        public static string TestReplace(string input, Dictionary<string, string> ReplacementRules)
        {
            HashSet<int> LockedStringSegment = new HashSet<int>();

            foreach (var rule in ReplacementRules)
            {
                string from = Regex.Escape(rule.Key);
                string to = rule.Value;
                var match = Regex.Match(input, from);
                if (match.Success)
                {
                    List<int> AffectedCharacterPositions = Enumerable.Range(match.Index, match.Length).ToList();

                    if (!AffectedCharacterPositions.Any(x => LockedStringSegment.Contains(x)))
                    {
                        input = Regex.Replace(input, from, to);
                        int LengthDelta = to.Length - rule.Key.Length;

                        LockedStringSegment
                            .Where(x => x > match.Index + rule.Key.Length).OrderByDescending(x => x).ToList()
                            .ForEach(x =>
                            {
                                //We shuffle the locked character's place depending on the replacement delta.
                                LockedStringSegment.Remove(x);
                                LockedStringSegment.Add(x + LengthDelta);
                            });

                        //Add all the new locked character's position to the hashset.
                        Enumerable.Range(match.Index, to.Length).ToList().ForEach(x => LockedStringSegment.Add(x));

                    }
                }
            }

            return input;
        }
        public static void ValidateReplacement(string MethodName, string Actual, string Expected)
        {
            Console.Write($"{MethodName} : ");

            if (Expected != Actual)
                Console.WriteLine("String replacement doesn't work");
            else
                Console.WriteLine("It works");

            Console.WriteLine($"Expected : {Expected}");
            Console.WriteLine($"Actual   : {Actual} nn");

        }

    }

}

I will do pencil sketch based on your photo within 24 hours for $3

I will do pencil sketch based on your photo within 24 hours

Hello
I will turn your photo into Pencil sketch.

(the better the photo quality the better the results)

Here I am providing you photo realistic sketches and drawings done within 24 hours.

Surely you’ll be happy with my work!

I Will make A Professionally Looking Pencil Sketch For You with Photoshop filter or a similar technique.

You can give me anything:

  • Portrait photos
  • Family photos
  • Animal photos
  • Any product photos
  • Landscape photos
  • Architecture photos
  • Anything you imagine

You will receive:

  • Only ONE OBJECT (from head to shoulder)
  • White background
  • Black and white sketch / color pencil sketch ( your choose )
  • Jpg or Png

Please contact me in inbox before placing the order
if you have any question, you can ask me without any hesitation
if you want my service,
Just place your Order…
Thank you

.(tagsToTranslate)drawing(t)pencil(t)sketch(t)portrait(t)artist

Universal browser based Dark Mode (Dark Reader) vs. Individual separate black themes on each site

I’m pondering an idea about browser based, concentrated dark mode/dark theme service’s that operate pretty much like current superb add-on Dark Reader.

What makes me wonder why does the community that usually optimizes for not repeating work among developers, does take the burden of developing a dark theme for pretty much every popular web application.

Wouldn’t it be wiser to develop a set of standards when it comes to colour management, maybe even a .darktheme config file to override some of the basic settings, alongside a single browser based dark mode program for every browser that operates on the principle of current dark reader? I mean the reader is so good that I pretty much prefer it to custom made dark modes on most of the sites anyway.

What’s your insight on this matter?

My personal opinion is that it’s the inevitable future, but I haven’t found any signs that the process has started yet.

EDIT- before someone suggests sticking to the Dark Reader extension, It’s all good but compare a little a bit clunky extension developed by few enthusiasts to fully fledged browser options backed by hundreds of full-time developers.

Thanks!

google pixel – Prevent Android from changing language based on location

I’m on vacation in a different country on a trip that did not include knowing a new language.

It’s not everywhere but enough to be annoying. Latest, changing language of buttons on a Google Form that’s never seen the language during its creation or previous uses. Before that, suggested videos on YouTube are all in Spanish.

Pixel 5 Android 11

software – Could you help me understand what a problem domain is and how can I build my class diagram based on it?

So I just started studying Software Engineering because I am really interested in it and my professor in London asked us to create an app which is like Instagram (only theoretically, without the actual implementation of code) and I would like some help on something.

I have started with writing and studying different parts, like risk analysis and everything and now I just created a class diagram with the classes that are in my opinion essential for the project. Like content, photos, users, display, etc. What I really haven’t understood is that we needed to draw the class diagram based on the problem domain of my case.

Maybe it’s because of my lower English level, but problem sounds literal to me and while some websites mention that the problem domain relates to the risks and the problems of your project, others say that the problem domain is whatever is essential for the creation of the app.

Can someone please elaborate on that? You will really help me get deeper into the project because it’s very confusing and they haven’t explained it so much at uni yet..

Thanks 🙂

How to load a node based on its title?

In Drupal 6, you can use the following code.

$node = node_load(array('title' => 'node title'));

If you know the node’s type as well, then you can use the following code.

$node = node_load(array('title' => 'node title', 'type' => 'page'));

node_load() always returns a single node. That is because the function doesn’t loop over the database query result resource returned from db_query().

  if ($revision) {
    array_unshift($arguments, $revision);
    $node = db_fetch_object(db_query('SELECT ' . $fields . ' FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.nid = n.nid AND r.vid = %d WHERE ' . $cond, $arguments));
  }
  else {
    $node = db_fetch_object(db_query('SELECT ' . $fields . ' FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.vid = n.vid WHERE ' . $cond, $arguments));
  }

python – How to make this DFS based exhaustive word grid search faster

I have implemented an exhaustive dfs based grid search that searches for all possible words in the 8 connected regions. Is there any way I can make this faster or memory efficient than it is now?

I feel like passing a copy of the seen set in dfs(xx, yy, seen.copy(), word + grid(x)(y)) is an overhead that can possibly be avoided.

Or is my algorithm completely wrong and I should use something else?

This is not a code site challenge, I am just trying to implement this out of curiosity.

My first question on code review so let me know if there is something that I can add to make this better.

Also can this be made more “pythonic”?

def possible_directions(x, y):
    """
    Returns at most 8 connected co-ordinates for given `x` and `y`
    """
    for xx in range(-1, 2):
        for yy in range(-1, 2):
            if not xx and not yy:
                continue
            if 0 <= x + xx < len(grid) and 0 <= y + yy < len(grid(x)):
                yield x + xx, y + yy

def dfs(x, y, seen, word = ''):
    """
    Recursive Generator that performs a dfs on the word grid
    seen = set of seen co-ordinates, set of tuples
    word = word to yield at each recursive call
    """
    if (x, y) in seen:
        return
    yield word + grid(x)(y)
    seen.add((x, y))
    for xx, yy in possible_directions(x, y):
        yield from dfs(xx, yy, seen.copy(), word + grid(x)(y))



grid = (('c', 'f', 'u', 'c'), ('e', 'a', 't', 'e'), ('b', 'h', 'p', 'y'), ('o', 'n', 'e', 'p')) # word grid, can be any size, n x n

for x in range(len(grid)):
    for y in range(len(grid(x))):
        for i in dfs(x, y, set()):
            print(i)