Thursday, February 27, 2025

 Given a wire grid of size N * N with N-1 horizontal edges and N-1 vertical edges along the X and Y axis respectively, and a wire burning out every instant as per the given order using three matrices A, B, C such that the wire that burns is

(A[T], B[T] + 1), if C[T] = 0 or

(A[T] + 1, B[T]), if C[T] = 1

Determine the instant after which the circuit is broken

     public static boolean checkConnections(int[] h, int[] v, int N) {

        boolean[][] visited = new boolean[N][N];

        dfs(h, v, visited,0,0);

        return visited[N-1][N-1];

    }

    public static void dfs(int[]h, int[]v, boolean[][] visited, int i, int j) {

        int N = visited.length;

        if (i < N && j < N && i>= 0 && j >= 0 && !visited[i][j]) {

            visited[i][j] = true;

            if (v[i * (N-1) + j] == 1) {

                dfs(h, v, visited, i, j+1);

            }

            if (h[i * (N-1) + j] == 1) {

                dfs(h, v, visited, i+1, j);

            }

            if (i > 0 && h[(i-1)*(N-1) + j] == 1) {

                dfs(h,v, visited, i-1, j);

            }

            if (j > 0 && h[(i * (N-1) + (j-1))] == 1) {

                dfs(h,v, visited, i, j-1);

            }

        }

    }

    public static int burnout(int N, int[] A, int[] B, int[] C) {

        int[] h = new int[N*N];

        int[] v = new int[N*N];

        for (int i = 0; i < N*N; i++) { h[i] = 1; v[i] = 1; }

        for (int i = 0; i < N; i++) {

            h[(i * (N)) + N - 1] = 0;

            v[(N-1) * (N) + i] = 0;

        }

        System.out.println(printArray(h));

        System.out.println(printArray(v));

        for (int i = 0; i < A.length; i++) {

            if (C[i] == 0) {

                v[A[i] * (N-1) + B[i]] = 0;

            } else {

                h[A[i] * (N-1) + B[i]] = 0;

            }

            if (!checkConnections(h,v, N)) {

                return i+1;

            }

        }

        return -1;

    }

        int[] A = new int[9];

        int[] B = new int[9];

        int[] C = new int[9];

        A[0] = 0; B [0] = 0; C[0] = 0;

        A[1] = 1; B [1] = 1; C[1] = 1;

        A[2] = 1; B [2] = 1; C[2] = 0;

        A[3] = 2; B [3] = 1; C[3] = 0;

        A[4] = 3; B [4] = 2; C[4] = 0;

        A[5] = 2; B [5] = 2; C[5] = 1;

        A[6] = 1; B [6] = 3; C[6] = 1;

        A[7] = 0; B [7] = 1; C[7] = 0;

        A[8] = 0; B [8] = 0; C[8] = 1;

        System.out.println(burnout(9, A, B, C));

1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0

1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0

8

Alternatively,

    public static boolean burnWiresAtT(int N, int[] A, int[] B, int[] C, int t) {

        int[] h = new int[N*N];

        int[] v = new int[N*N];

        for (int i = 0; i < N*N; i++) { h[i] = 1; v[i] = 1; }

        for (int i = 0; i < N; i++) {

            h[(i * (N)) + N - 1] = 0;

            v[(N-1) * (N) + i] = 0;

        }

        System.out.println(printArray(h));

        System.out.println(printArray(v));

        for (int i = 0; i < t; i++) {

            if (C[i] == 0) {

                v[A[i] * (N-1) + B[i]] = 0;

            } else {

                h[A[i] * (N-1) + B[i]] = 0;

            }

        }

        return checkConnections(h, v, N);

    }

    public static int binarySearch(int N, int[] A, int[] B, int[] C, int start, int end) {

        if (start == end) {

            if (!burnWiresAtT(N, A, B, C, end)){

                return end;

            }

            return -1;

        } else {

            int mid = (start + end)/2;

            if (burnWiresAtT(N, A, B, C, mid)) {

                return binarySearch(N, A, B, C, mid + 1, end);

            } else {

                return binarySearch(N, A, B, C, start, mid);

            }

        }

    }

1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0

1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0

8


Wednesday, February 26, 2025

 #codingexercise

Get the widest difference between two indexes in an array of random integers such that the integer at the second index is greater than that of the first.

import java.util.Arrays;

public class LongestIncreasingSubsequence {

    public static void main(String[] args) {

        int[] arr = {10, 22, 9, 33, 21, 50, 41, 60, 80};

        System.out.println(GetWidestDifferenceBetweenIndicesOfLIS(arr));

    }

    public static int GetWidestDifferenceBetweenIndicesofLIS(int[] arr) {

        int n = arr.length;

        int[] lis = new int[n+1];

        for (int i = 0; i < n+1; i++) {

                lis[i] = 1; // Initialize LIS value for each element

        }

        for (int i = 1; i < n; i++) {

            for (int j = 0; j < i; j++) {

                if (arr[i] > arr[j]) {

                    lis[i] = Math.max(lis[i], lis[j] + 1);

                }

            }

        }

       // lis = 1,2,1,3,2,4,4,5,6

        int max_length = Arrays.stream(lis).max().orElse(0);

        if (max_length == 0) return 0;

        if (max_length == 1) return 1;

        int last = -1;

        for (int i = n-1; i >= 0; i++) {

              if (lis[I] == max_length ) {

                    last = i;

              }

        }

        int first = 0;

        for (int i = 0; i < n; i++) {

             if (lis[i] == 2) {

                 first = i;

                 break;

             }

        }

        for(int I = 0; I < first; i++) {

               if (arr[i] < arr[first]) {

                    first = I;

                    break;

               }

        }

        Return last-first+1;

    }

}

9

#booksummary:  BookSummary233.docx

Tuesday, February 25, 2025

 Problem statement: Given the root of a binary tree, check whether it is a mirror of itself (i.e., symmetric around its center).

Example 1:

                 1

                2 2

             3 4 4 3

Input: root = [1,2,2,3,4,4,3]

Output: true

Example 2:

                 1

                2 2

       null 3 null 3

Input: root = [1,2,2,null,3,null,3]

Output: false

Constraints:

The number of nodes in the tree is in the range [1, 1000].

-100 <= Node.val <= 100

/**

 * Definition for a binary tree node.

 * public class TreeNode {

 * int val;

 * TreeNode left;

 * TreeNode right;

 * TreeNode() {}

 * TreeNode(int val) { this.val = val; }

 * TreeNode(int val, TreeNode left, TreeNode right) {

 * this.val = val;

 * this.left = left;

 * this.right = right;

 * }

 * }

 */

class Solution {

    public boolean isSymmetric(TreeNode root) {

        List<Integer> serialized = new ArrayList<Integer>();

        InorderTraversal(root, serialized);

        return IsPalindrome(serialized);

    }

    public boolean isPalindrome(List<Integer> serialized) {

        int i = 0;

        int j = serialized.count() - 1;

        while (i < j) {

            if (serialized.getAt(i) != serialized.getAt(j)) {

                return false;

            }

            i++;

            j--;

        }

        return true;

    }

    public void InorderTraversal(TreeNode root, List<Imteger> serialized) {

        if (root == null) {

            serialized.Add(Integer.MinValue);

            return;

        }

        InOrderTraversal(root.left);

        serialized.Add(root.val);

        InOrderTraversal(root.right);

    }

}


Monday, February 24, 2025

  Infrastructure engineering for AI projects often deal with text based inputs for analysis and predictions whether they are sourced from chatbots facing the customers, service ticket notes, or a variety of data stores and warehouses but the ability to convert text into audio format is also helpful for many scenarios, including DEI requirements and is quite easy to setup and offers the convenience to listen when screens are small, inadequate for usability and are difficult to read. Well-known audio formats can be played on any device and not just phones.


Although there are many dedicated text-to-speech software and online services available, some for free, and with programmability via web requests, there are built-in features of the public cloud service portfolio that make such capabilities more mainstream and at par with the rest of the AI/ML pipelines. This article includes one such implementation towards the end but first an introduction to this feature and capabilities as commercially available.


Most audio is characterized by amplitude as measured in decibels preferably at least 24db, the stream or bit rate which should preferably be at least 192kbps, and a limiter. Different voices can be generated with variations in amplitude, pitch, and tempo much like how singing is measured. Free text-to-speech software, whether standalone, or online gives conveniences in the input and output formats. For example, Natural Reader allows you to load documents and convert them to audio files. Balabolka can save narrations as a variety of audio file formats with customizations for pronunciations and voice settings. Panopreter Basic, also free software, add both input formats and mp3 output format. TTSMaker supports 100+ languages and 600+ AI voices for commercial purposes. Murf AI although not entirely free has a converter that supports 200+ realistic AI voices and 20+ languages. Licensing and distribution uses varies with each software maker.


Public-cloud based capabilities for text-to-speech can be instantiated with a resource initialization from the corresponding service in their service portfolio. The following explains just how to do that.


Sample implementation:


1. Get text input over a web api:


from flask import Flask, request, jsonify, send_file


import os


import azure.cognitiveservices.speech as speechsdk


app = Flask(__name__)


# Azure Speech Service configuration


SPEECH_KEY = "<your-speech-api-key>"


SERVICE_REGION = "<your-region>"


speech_config = speechsdk.SpeechConfig(subscription=SPEECH_KEY, region=SERVICE_REGION)


speech_config.set_speech_synthesis_output_format(speechsdk.SpeechSynthesisOutputFormat.Audio16Khz32KBitRateMonoMp3)


speech_config.speech_synthesis_voice_name = "en-US-GuyNeural" # Set desired voice


@app.route('/text-to-speech', methods=['POST'])


def text_to_speech():


    try:


        # Check if text is provided directly or via file


        if 'text' in request.form:


            text = request.form['text']


        elif 'file' in request.files:


            file = request.files['file']


            text = file.read().decode('utf-8')


        else:


            return jsonify({"error": "No text or file provided"}), 400


        # Generate speech from text


        audio_filename = "output.mp3"


        file_config = speechsdk.audio.AudioOutputConfig(filename=file_name)


        synthesizer = speechsdk.SpeechSynthesizer(speech_config=speech_config, audio_config=file_config)


        result = synthesizer.speak_text_async(text).get()


        if result.reason == speechsdk.ResultReason.SynthesizingAudioCompleted:


            # Save audio to file


            with open(audio_filename, "wb") as audio_file:


                audio_file.write(result.audio_data)


            return send_file(audio_filename, as_attachment=True)


        else:


            return jsonify({"error": f"Speech synthesis failed: {result.reason}"}), 500


    except Exception as e:


        return jsonify({"error": str(e)}), 500


if __name__ == "__main__":


    app.run(host="0.0.0.0", port=5000)


2. Prerequisites to run the script:


a. pip install flask azure-cognitiveservices-speech


b. Create an Azure Speech resource in the Azure portal and retrieve the SPEECH_KEY and SERVICE_REGION from the resources’ keys and endpoint section and use them in place of `<your-speech-api-key>` and `<your-region>` above


c. Save the script and run it in any host as `python app.py`


3. Sample trial


a. With curl request as `curl -X POST -F "text=Hello, this is a test." http://127.0.0.1:5000/text-to-speech --output output.mp3`


b. Or as file attachment with `curl -X POST -F "file=@example.txt" http://127.0.0.1:5000/text-to-speech --output output.mp3`


c. The mp3 audio file generated can be played.


Sample output: https://b67.s3.us-east-1.amazonaws.com/output.mp3


Pricing: Perhaps the single most sought-after feature on text-to-speech is the use of natural sounding voice and service providers often markup the price or even eliminate programmability options for the range of the natural-voices offered. This severely limits the automation of audio books. A comparison of costs might also illustrate the differences between the service providers. Public Cloud text-to-speech services typically charge $4 and $16 per million characters for standard and neural voices respectively which is about 4-5 audio books. Custom voices require about $30 per million characters while dedicated providers such as Natural Voice with more readily available portfolio of voices charge about $60/month as a subscription fee and limits on words. This is still costly but automation of audio production for books is here to stay simply because of the time and effort saved.





Sunday, February 23, 2025

 Problem: Count the number of ways to climb up the staircase and we can modify the number of steps at any time to 1 or 2

Solution: int getCount(int n)

{

    int [] dp = new int[n+2];

    dp [0] = 0;

    dp [1] = 1;

    dp [2] = 2;

    for (int k = 3; k <= n; k++) {

                 dp [k] = dp [k-1] + dp [k-2];

    }

   return dp [n];

}

Problem: Rotate a n x n matrix by 90 degrees:

Solution:

static void matrixRotate(int[][] A, int r0, int c0, int rt, int ct)

        {

            if (r0 >= rt) return;

            if (c0 >= ct) return;

            var top = new int[ct-c0+1];

            int count = 0;

            for (int j = 0; j <= ct-c0; j++){

                  top[count] = A[0][j];

                  count++;

            }

            count--;

            for (int j = ct; j >= c0; j--)

            A[c0][j] = A[ct-j][0];

            for (int i = r0; i <= rt; i++)

            A[i][c0] = A[rt][i];

            for (int j = c0; j <= ct; j++)

            A[rt][j] = A[ct-j][ct];

            for (int i = rt; i >= r0; i--) {

                   A[i][ct] = top[count];

                   count--;

            }

            matrixRotate(A, r0+1, c0+1, rt-1, ct-1);

        }

// Before:

1 2 3

4 5 6

7 8 9

// After:

7 4 1

8 5 2

9 6 3

// Before

1 2

3 4

// After

3 1

4 2


Saturday, February 22, 2025

 This is a summary of the book titled “Raising an entrepreneur: 10 rules for nurturing Risk takers, Problem Solvers, and Change Makers” written by Margot Machol Bisnow and published by New Harbinger in 2016. The author is a businesswoman and a mom who cautions parents from being overprotective and instead suggests to guide their children gently in discovering their strengths and passions, give them enormous freedom, letting them fail and face adversity in a way that builds resilience, selflessness, determination and “grit”. These very traits make successful entrepreneurs. Although the right balance is hard to find and it might seem that the author contradicts herself as she leans from side to side, she advocates for raising independent successful kids and support their dreams. Arming the children with morals, values, and a commitment to help others, installing grit, encouraging perseverance and hard work, emphasizing purpose over financial security, giving them freedom to do it their way, making mistakes and learning from them, letting them figure out, and praising and recognizing them are some of her other suggestions.

Parents should support and believe in their children's potential as entrepreneurs or employees. Encourage them to explore various ideas, fields, and career choices, and not force them into "safe" fields. Encourage them to discover their passions and excel in their chosen fields, regardless of whether they lead to conventional jobs or financial security. Instill morals, values, and a sense of mission in them to push through the work world's setbacks and disappointments.

Nurturing future entrepreneurs requires parents to believe in them, support them, and stand up for them. Encourage their passions, regardless of their preferences, and support their curiosity as they explore different things. For example, Michael Chasen's passion for video games led him to earn an MBA and start Blackboard, a tech firm he sold for $1.5 billion. Parents should also set boundaries, such as no TV on weekdays, and support their children's decision to start a business instead of going to college.

Parents should trust their children's passions and encourage them to pursue their passions without intervention. Paige Mycoskie's parents believed in her love for art and encouraged her to build a clothing company with her savings. Jayne Plank, despite her heavy workload, encouraged her children's passions early on, giving them freedom to pursue their passions.

Let your child learn to win and lose without intervention, introducing them to a wide range of sports and activities. Sports help kids understand that failure doesn't define them and increases their risk tolerance. Parents should also give their children a chance to figure out their passion, even if it's not something they would have chosen.

Successful entrepreneurs can range from academic misfits to college dropouts, so parents should support their children's interests at school as they support their sports and other pursuits. Avoid pushing kids towards math and science if their passion is art, and don't listen to teachers and principals who want their child to conform.

Parents should encourage their children to pursue their passions and pursue their passions, even if it means changing schools or standing up to teachers and administrators. They should also support their children in pursuing their academic areas they love, even if that means changing schools or standing up to teachers and administrators. Mentors can be great in guiding future entrepreneurs, as they can offer harsher feedback and provide a different perspective than parents can bring themselves. Parents should trust their children, give them freedom, and offer genuine praise, avoiding presenting them with "trophies" for everything they do. They should guide them to persevere through failure and adversity, as their confidence will grow each time they overcome a challenge. Embracing adversity can build character and resilience, as seen with Sean Stephenson, who graduated from college, interned for President Bill Clinton, and now advises executives nationwide. By fostering these qualities, parents can help their children become successful entrepreneurs and contribute to the growth of their communities. To foster a positive future for your children, it is essential to nurture compassion, encourage competitiveness, and be a great family. Encourage your children to help others and challenge conventions, as it is difficult to sustain their efforts solely for personal gain. Be a great family by providing a supportive environment and allowing your kids to pursue their passions. Instill awareness of a larger purpose in your children, using spiritual alternatives or faith as a compass.

Lead by following, allowing your children to chart their own path and focus on what they want to do. Encourage them to try many things until they find what inspires them. Don't raise your kids to feel entitled, but to work hard, persevere, and fight for a better future for themselves and others. Remember, loving your children is not the same as trusting and believing in them.

#codingexercise: CodingExercise-02-22-2025.docx

Thursday, February 20, 2025

Exercises

 Predict the Number

Programming challenge description:

The example sequence 011212201220200112 ... is constructed as follows:

1. The first element in the sequence is 0.

2. For each iteration, repeat the following action: take a copy of the entire current sequence, replace 0 with 1, 1 with 2, and 2 with 0, and place it at the end of the current sequence. E.g.

0 -> 01 -> 0112 -> 01121220 -> ...

Create an algorithm which determines what number is at the Nth position in the sequence (using 0-based indexing).

Input:

Your program should read lines from standard input. Each line contains an integer N such that 0 <= N <= 3000000000.

Output:

Print out the number which is at the Nth position in the sequence.

Test 1

Test Input

Download Test 1 Input

5

Expected Output

Download Test 1 Output

2

Test 2

Test Input

Download Test 2 Input

25684

Expected Output

Download Test 2 Output

0

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStreamReader;

import java.nio.charset.StandardCharsets;

public class Main {

  private static int getDigitAt(long position) {

    StringBuilder sb = new StringBuilder("0");

    int result = -1;

    long start = 1;

    for (long i = 0; i < Long.MAX_VALUE && start-1 <= position; i++) {

            String candidate = sb.toString();

            candidate = candidate.replace("0", "X").replace("1", "Y").replace("2","Z").replace("X","1").replace("Y", "2").replace("Z", "0");

            start += candidate.length();

            sb.append(candidate);

    }

    result = Integer.parseInt(String.valueOf(sb.charAt((int)position)));

    return result;

  }

  public static void main(String[] args) throws IOException {

    InputStreamReader reader = new InputStreamReader(System.in, StandardCharsets.UTF_8);

    BufferedReader in = new BufferedReader(reader);

    String line;

    while ((line = in.readLine()) != null) {

      long position = Long.MAX_VALUE;

      try {

        position = Long.parseLong(line);

      } catch (NumberFormatException e) {

      }

      if (position != Long.MAX_VALUE) {

        int digit = getDigitAt(position);

        System.out.println(digit);

      }

    }

  }

}

Running test cases... Done

– – – – – – – – – – – – – –

Test 1

Passed Collapse

Test Input:

5

Expected Output:

2

Your Output:

2

Test 2

Passed Collapse

Test Input:

25684

Expected Output:

0

Your Output:

0

Problem 2:

You are painting a fence of n posts with k different colors. You must paint the posts following these rules:

• Every post must be painted exactly one color.

• There cannot be three or more consecutive posts with the same color.

Given the two integers n and k, return the number of ways you can paint the fence.

Example 1:

Input: n = 3, k = 2

Output: 6

Explanation: All the possibilities are shown.

Note that painting all the posts red or all the posts green is invalid because there cannot be three posts in a row with the same color.

Example 2:

Input: n = 1, k = 1

Output: 1

Example 3:

Input: n = 7, k = 2

Output: 42

Constraints:

• 1 <= n <= 50

• 1 <= k <= 105

• The testcases are generated such that the answer is in the range [0, 231 - 1] for the given n and k.

Solution:

class Solution {

    public int numWays(int n, int k) {

     int[] dp = new int[n+2];

     dp[0] = 0;

     dp[1] = k;

     dp[2] = k + k*(k-1);

     for (int i = 3; i <=n; i++) {

         dp[i] = dp[i-1] * (k-1) + dp[i-2]*(k-1);

     }

     return dp[n];

    }

}

Accepted

Runtime: 0 ms

Case 1

Case 2

Case 3

Input

n =

3

k =

2

Output

6

Expected

6

Input

n =

1

k =

1

Output

1

Expected

1

Input

n =

7

k =

2

Output

42

Expected

42

Problem #3:

Make Array Zero by Subtracting Equal Amounts

You are given a non-negative integer array nums. In one operation, you must:

• Choose a positive integer x such that x is less than or equal to the smallest non-zero element in nums.

• Subtract x from every positive element in nums.

Return the minimum number of operations to make every element in nums equal to 0.

Example 1:

Input: nums = [1,5,0,3,5]

Output: 3

Explanation:

In the first operation, choose x = 1. Now, nums = [0,4,0,2,4].

In the second operation, choose x = 2. Now, nums = [0,2,0,0,2].

In the third operation, choose x = 2. Now, nums = [0,0,0,0,0].

Example 2:

Input: nums = [0]

Output: 0

Explanation: Each element in nums is already 0 so no operations are needed.

Constraints:

• 1 <= nums.length <= 100

• 0 <= nums[i] <= 100

import java.util.*;

import java.util.stream.*;

class Solution {

    public int minimumOperations(int[] nums) {

        List<Integer> list = Arrays.stream(nums).boxed().collect(Collectors.toList());

        var nonZero = list.stream().filter(x -> x > 0).collect(Collectors.toList());

        int count = 0;

        while(nonZero.size() > 0) {

            var min = nonZero.stream().mapToInt(x -> x).min().getAsInt();

            nonZero = nonZero.stream().map(x -> x - min).filter(x -> x > 0).collect(Collectors.toList());

            count++;

        }

        return count;

    }

}

Input

nums =

[1,5,0,3,5]

Output

3

Expected

3

Input

nums =

[0]

Output

0

Expected

0

SQL Schema

Table: Books

+----------------+---------+

| Column Name | Type |

+----------------+---------+

| book_id | int |

| name | varchar |

| available_from | date |

+----------------+---------+

book_id is the primary key of this table.

Table: Orders

+----------------+---------+

| Column Name | Type |

+----------------+---------+

| order_id | int |

| book_id | int |

| quantity | int |

| dispatch_date | date |

+----------------+---------+

order_id is the primary key of this table.

book_id is a foreign key to the Books table.

Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than one month from today. Assume today is 2019-06-23.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:

Books table:

+---------+--------------------+----------------+

| book_id | name | available_from |

+---------+--------------------+----------------+

| 1 | "Kalila And Demna" | 2010-01-01 |

| 2 | "28 Letters" | 2012-05-12 |

| 3 | "The Hobbit" | 2019-06-10 |

| 4 | "13 Reasons Why" | 2019-06-01 |

| 5 | "The Hunger Games" | 2008-09-21 |

+---------+--------------------+----------------+

Orders table:

+----------+---------+----------+---------------+

| order_id | book_id | quantity | dispatch_date |

+----------+---------+----------+---------------+

| 1 | 1 | 2 | 2018-07-26 |

| 2 | 1 | 1 | 2018-11-05 |

| 3 | 3 | 8 | 2019-06-11 |

| 4 | 4 | 6 | 2019-06-05 |

| 5 | 4 | 5 | 2019-06-20 |

| 6 | 5 | 9 | 2009-02-02 |

| 7 | 5 | 8 | 2010-04-13 |

+----------+---------+----------+---------------+

Output:

+-----------+--------------------+

| book_id | name |

+-----------+--------------------+

| 1 | "Kalila And Demna" |

| 2 | "28 Letters" |

| 5 | "The Hunger Games" |

+-----------+--------------------+

SELECT DISTINCT b.book_id, b.name

FROM books b

LEFT JOIN Orders o on b.book_id = o.book_id

GROUP BY b.book_id, b.name,

DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date),

DATEDIFF(day, b.available_from, DATEADD(month, -1, '2019-06-23'))

HAVING SUM(o.quantity) IS NULL OR

DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date) < 0 OR

(DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date) > 0 AND DATEDIFF(day, b.available_from, DATEADD(month, -1, '2019-06-23')) > 0 AND SUM(o.quantity) < 10);

Case 1

Input

Books =

| book_id | name | available_from |

| ------- | ---------------- | -------------- |

| 1 | Kalila And Demna | 2010-01-01 |

| 2 | 28 Letters | 2012-05-12 |

| 3 | The Hobbit | 2019-06-10 |

| 4 | 13 Reasons Why | 2019-06-01 |

| 5 | The Hunger Games | 2008-09-21 |

Orders =

| order_id | book_id | quantity | dispatch_date |

| -------- | ------- | -------- | ------------- |

| 1 | 1 | 2 | 2018-07-26 |

| 2 | 1 | 1 | 2018-11-05 |

| 3 | 3 | 8 | 2019-06-11 |

| 4 | 4 | 6 | 2019-06-05 |

| 5 | 4 | 5 | 2019-06-20 |

| 6 | 5 | 9 | 2009-02-02 |

| 7 | 5 | 8 | 2010-04-13 |

Output

| book_id | name |

| ------- | ---------------- |

| 2 | 28 Letters |

| 1 | Kalila And Demna |

| 5 | The Hunger Games |

Expected

| book_id | name |

| ------- | ---------------- |

| 1 | Kalila And Demna |

| 2 | 28 Letters |

| 5 | The Hunger Games |