This is a summary of the book titled “Leader as Healer” written by Nicholas Janni and published by LID Publishing in 2022. This book is an interesting take on leadership from a person who emphasizes spirituality, mental healing, mindfulness, consciousness and peak performance from a Zen-like state. He is unusual as a business leader and author because he teaches acting and heads his own theatre company. He says that innovative leaders should tap into enlightened consciousness. Today’s problems require a heightened awareness and leaders who are aware and empathetic can be more than just executors. They can become emotional and spiritual healers and the journey begins with self-care and leading a purposeful life.

Technology has transformed the world, and heightened consciousness can transform individuals. CEOs and senior executives must invest their energy in innovative, mindful leadership to address today's challenges. Major disruptions are impacting industries, and society must prioritize communal, intuitive, and metaphysical principles over greed, self-interest, and competition. To fix today's problems, leaders must cultivate higher awareness and become emotional and spiritual healers.

Leaders who practice elevated thinking, being, and acting can become emotional and spiritual healers, inspiring others and transforming fragmented organizations into "coherent wholes." They can reawaken a company's vitality, build internal connections, and imbue an organization with energy. The "Leader-as-healer" model is an advanced leadership construct that replaces the outdated "Leader as executor" model, which prioritizes profits, compelled action, and discipline.

In today's fractious times, innovative leaders and a radical attitude adjustment are needed to address the challenges and promote a more compassionate and sustainable world.

The "Leader as executor" role is outdated and should be replaced by "Leader-as-healer." This model prioritizes profit, discipline, and action, but does not address the human side of business. Leaders-as-healers have practical wisdom and recognize the value of giving focused attention to their leaders, providing a "coherent presence" and showing employees that they are always ready to help.

To cope effectively with today's complex, disruptive world, leaders and employees must practice self-care, paying close attention to their emotional, spiritual, and physical needs, including their health. True leadership sometimes requires excising moral and spiritual tumors from organizations or nations. To tune into one's body and emotions, consider following a somatic mindfulness practice, paying close attention to breathing, and focusing on the body's inner and outer sensations.

Simple awareness exercises can help develop significant personal insights and rewire neural pathways, allowing leaders to better understand and navigate the challenges of today's complex world. By practicing self-care, leaders can become more receptive to new ideas and maintain an open spirit.

Self-care means paying close attention to one’s emotional, spiritual, and physical needs, including one’s health. When leaders do this, they develop significant personal insights and this helps to deepen the scope of their leadership. Mindfulness is practiced by focusing on their minds with contemplative exercises such as meditation which dates back to pre-Colombian civilization, early Hebrew mystics, Indian yogis, and Native American shamans. We set aside 20 minutes daily for meditation, taking notes on our inner emotional, mental, and physical state.

Lead a life of purpose – the right purpose – to avoid navigating life like a ship in stormy waters without a compass or rudder. A leader who operates unemotionally, based only on rationality, might translate purpose as increased profits and productivity, but their life's purpose should be internally worthwhile. Define your values and consider the contributions you can make to improve the world.

A strong sense of purpose is crucial for navigating life effectively. A leader should focus on internal, worthwhile goals rather than external ones. Emotions are the gateway to deeper humanity and a richer, more heartfelt relationship with life and leadership. Leaders should embrace their emotional side and abandon archaic thinking that has failed in the past. They should practice enlightened leadership that fortifies organizations and employees by combining thinking and feeling facets of themselves.

#codingexercise

Position eight queens on a chess board without conflicts:

    public static void positionEightQueens(int[][] B, int[][] used, int row) throws Exception {

        if (row == 8) {

            if (isAllSafe(B)) {

                printMatrix(B, B.length, B[0].length);

            }

            return;

        }

        for (int k = 0; k < 8; k++) {

            if ( isSafe(B, row, k) && isAllSafe(B)) {

                B[row][k] = 1;

                positionEightQueens(B, used, row + 1);

                B[row][k]  = 0;

            }

        }

    }

    public static boolean isSafe(int[][] B, int p, int q) {

        int row = B.length;

        int col = B[0].length;

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

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

                if (i == p && j == q) { continue; }

                if (B[i][j] == 1) {

                    boolean notSafe = isOnDiagonal(B, p, q, i, j) ||

                            isOnVertical(B, p, q, i, j) ||

                            isOnHorizontal(B, p, q, i, j);

                    if(notSafe){

                        return false;

                    }

                }

             }

        }

        return true;

    }
    public static boolean isAllSafe(int[][] B) {

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

            for (int j = 0; j < B[0].length; j++) {

                if (B[i][j]  == 1 && !isSafe(B, i, j)) {

                    return false;

                }

            }

        }

        return true;

    }

    public static boolean isOnDiagonal(int[][] used, int r1, int c1, int r2, int c2) {

        boolean result = false;

        int row = used.length;

        int col = used[0].length;

        for (int k = 0; k < 8; k ++) {

            if (r2 - k >= 0 &&  c2 - k >= 0 && r1 == r2 - k && c1 == c2 - k) {

                return true;

            }

            if (r2 + k < row && c2 + k < col && r1 == r2 + k && c1 == c2 + k) {

                return true;

            }

            if (r2 - k >= 0 && c2 + k < col && r1 == r2 - k && c1 == c2 + k) {

                return true;

            }

            if (r2 + k < row  && c2 - k >= 0 && r1 == r2 + k && c1 == c2 - k) {

                return true;

            }

        }

        return result;

    }

    public static boolean isOnVertical(int[][] used, int r1, int c1, int r2, int c2) {

        boolean result = false;

        int row = used.length;

        int col = used[0].length;

        for (int k = 0; k < 8; k++) {

            if (c2 - k >= 0  && c1 == c2 - k && r1 == r2 ) {

                return true;

            }

            if (c2 + k < row && c1 == c2 + k && r1 == r2) {

                return true;

            }

        }

        return result;

    }

    public static boolean isOnHorizontal(int[][] used, int r1, int c1, int r2, int c2) {

        boolean result = false;

        int row = used.length;

        int col = used[0].length;

        for (int k = 0; k < 8; k++) {

            if (r2 - k >= 0  && r1 == r2 - k && c1 == c2 ) {

                return true;

            }

            if (r2 + k < row && r1 == r2 + k && c1 == c2) {

                return true;

            }

        }

        return result;

    }

 

Sample output:

1 1 2 1 1 1 1 1
1 1 1 1 1 2 1 1
1 1 1 2 1 1 1 1
1 2 1 1 1 1 1 1
1 1 1 1 1 1 1 2
1 1 1 1 2 1 1 1
1 1 1 1 1 1 2 1
2 1 1 1 1 1 1 1

 This is a summary of the book “Never say whatever: How small decisions make a big difference?” written by Richard A. Moran and published by McGraw-Hill in 2023. The author hosts a CBS syndicated radio program and is a venture capitalist. He contends that indifference is a waste and often loses its essence and purpose. He calls this writing an “anti-whatever workbook” and advocates empathy saying that we must be social and not just need-based like creatures. Detachment has a high correlation with losing direction and caring while making intelligent choices makes us better and even minor decisions are vital. Among the highlights of the book, one can find wisdom such as starting early with even high school students who are rarely taught how to make decisions. Smart decision makers are self-aware and trust their guts. Saying whatever is not only self-damaging, but also damaging the workplace we are in. Entrepreneurs just cannot have a “whatever” attitude and people with such attitude aggravate everyone. Often indifferent people come to regret their careless attitude. Good advisors can help us make sound decisions. Our choices shape our future so we must be decisive.

"Whatever" is a common term used by Americans to express disengagement and dismissal, affecting not only the speaker but also others around them. This attitude is risk-averse and hinders decision-making, as decisions and actions make life meaningful. Intentional people understand that even minor decisions can be vital, and they make choices with forethought, intention, and purpose. Decision-making can be simplified by sticking with the status quo, making minor adjustments, or doing a complete about-face. Intentions always point the way to actions and results, and a clear intention leads to sound choices. For example, if you want to secure a raise or a promotion, you must ask for it, practice it, and establish a deliberative process that makes the most of your strengths. A "whatever" attitude does not ensure the same number of planes take off and land each day.

High school students often lack the skills to make intelligent decisions, which are crucial for making major choices such as college, career, and marriage. To teach students the basics of intelligent decision-making, schools should teach them the importance of "whatever" and frame options as "if/then" scenarios. A 10-year study by Bain & Company found that organizations that make timely, intelligent decisions outperform those that appear indecisive. The "two-minute rule" demonstrates the power of quick, effective decision-making when there are small choices, but the appropriate amount of time and consideration should be given to all major decisions. Smart decision-makers are self-aware and trust their guts, knowing their strengths and weaknesses and their emotions. They recognize the consequences of their decisions and actions and are willing to make "gut" decisions under the right circumstances. A "whatever" attitude can damage careers and organizations, as it can undermine a company's direction and undermine employees' sense of purpose.

Entrepreneurs must not have a "whatever" attitude to succeed, as they must be nonstop decision-makers who can make timely and cost-effective choices. They often use pattern recognition to clarify their decision-making and draw analogies between challenges and past ones. "Whatever" people can aggravate everyone, leading to trouble and strife at work and home. Housemates who struggle with making decisions can be troublemakers.

Regret often arises from careless attitudes, as long-term regrets often stem from decisions we did not make. What we choose to do today often affects what is possible in the future. Small but bad choices can snowball. It is important to remember that every choice creates a "ripple effect" that affects other aspects of life in positive and negative ways. Every decision involves some degree of risk, and it is better to make a good decision than to do nothing. Consulting with trusted advisors can help make sound decisions and creating a "personal board of directors" with wise people can provide numerous expert perspectives. Finally, the author recommends us to be decisive and avoid using the word "whatever" to indicate that we do not care. This will ensure that our choices shape our future. Sam Alemayhu, a respected businessman, and investor grew up in an impoverished village in Ethiopia and emigrated to America. His accomplishments inspire others to be decisive, care about their choices, and never say "whatever."

#codingexercise

Find minimum in a rotated sorted array:

class Solution {

public int findMin(int[] A) {

If (A == null || A.length == 0) { return Integer.MIN_VALUE; }

int start = 0;

int end = A.length -1;

while (start < end) {

int mid = (start + end) / 2;

// check monotonically increasing series

if (A[start] <= A[end] && A[start] <= A[mid] && A[mid] <= A[end]]) { return A[start];};

// check if only [start, end]

if (mid == start || mid == end) { if (A[start] < A[end]) return A[start]; else return A[end];}

// detect rotation point

if (A[start] > A[mid]){

end = mid;

} else {

if (A[mid] > A[mid+1]) return A[mid+1];

start = mid + 1;

}

}

return A[0];

}

}

Works for:

[0 1 4 4 5 6 7]

[7 0 1 4 4 5 6]

[6 7 0 1 4 4 5]

[5 6 7 0 1 4 4]

[4 5 6 7 0 1 4]

[4 4 5 6 7 0 1]

[1 4 4 5 6 7 0]

[1 0 0 0 0 0 1]

 This is a continuation of a study involving a software application that responds to a chat like query on the data contained in the ten-year collection of my blog posts from https://ravinote.blogspot.com. Each article collected on the blog post is a daily routine and contains mostly unstructured text on quality explanations of software engineering practices and code samples from personal, enterprise and cloud computing. The earlier part of the study referred to leveraging Azure OpenAI search service to perform a semantic search based on the chat like query to create a generated response. This part of the study follows up on taking the data completely private so that the model built to respond to the query can be hosted on any lightweight compute including handheld devices using mobile browsers. The lessons learned in this section now follows:

First, a brief introduction of the comparison of the search methodologies between the two: 

1. Azure AI Toolkit for VS Code:

o Approach: This simplifies generative AI app development by bringing together AI tools and models from Azure AI catalog. We specifically use the Phi-2 small language model. It also helps to fine-tune and deploy models to the cloud.

o Matching: It matches based on similarity between query vectors and content vectors. This enables matching across semantic or conceptual likeness (e.g., “dog” and “canine”). Phi-2 is a 2.7 billion-parameter language model not the order of trillions in large language model but sufficiently compact to demonstrate outstanding reasoning and language understanding capabilities. Phi-2 is a Transformer based model with a next-word prediction objective that was originally trained on a large mixture of synthetic datasets for NLP and coding. 

o Scenarios Supported: 

 Find a supported model from the Model Catalog.

 Test model inference in the Model Playground.

 Fine-Tune model locally or remotely in Model Fine-tuning

 Deploy fine-tuned models to cloud via command-palette for AI Toolkit.

o Integration: Works seamlessly with other Azure services.

2. Azure OpenAI Service-Based Search:

o Approach: Uses the Azure OpenAI embedding model such as GPT-models to convert queries into vector embeddings. GPT-models are large language models while Phi-2 models are small language models. The dataset can include the web for the Chat Completions API from Azure OpenAI service. 

o Matching: Performs vector similarity search using the query vector in the vector database usually based on the top k-matching content based on a defined similarity threshold.

o Scenarios supported: 

 Similarity search: Encode text using embedding models (e.g., OpenAI embeddings) and retrieve documents with encoded queries.

 Hybrid search: Execute vector and keyword queries in the same request, merging results.

 Filtered vector search: Combine vector queries with filter expressions.

o Cost:

 increases linearly even for infrequent use at the rate of few-hundred dollars per month. The earlier application leveraging completion API had to be taken down for this reason.

Both approaches leverage vector embeddings for search, but toolkit and Phi-2 model are better for customizations while Azure OpenAI Completions API is useful for streamlined applications and quick chatbots.

And now the learnings follow:

- Fine-Tuning: A pretrained transformer can be put to different use during fine-tuning such as question-answering, language generation, sentiment analysis, summarization, and others. Fine-tuning adapts the model to different domains. Phi-2 behaves remarkably well in this regard. Fine-tuning LLMs are so cost prohibitive that it can be avoided. On the other hand, small language models are susceptible to overfitting where the model learns specifics of the training data that cannot be applied to the query.

- Parameter-Efficient Fine-tuning: This is called out here only for rigor. Costs for tuning LLMs can be reduced by fine-tuning only a subset of the model’s parameter but it might result in “catastrophic forgetting”. These techniques include LoRA, prefix tuning and prompt tuning. The Azure Toolkit leverages QLoRA. LoRA stands for Low-Rank Adaptation of Large Language Models and introduces trainable rank decomposition matrices into each layer of transformer architecture. It also reduces trainable parameters for downstream tasks while keeping the pre-trained weights frozen. QLoRA combines quantization with LORA with quantization data types to use as one of nf4 (4-bit normal float) or fp4 and adjustable batch size for training and evaluation per GPU. The data type is for compression to 4-bit precision as opposed to native floating-point-32-bit precision.

- Problem with outliers:  As with all neural networks that create embeddings, outliers are significant because while model weights are normally distributed, the inclusion of outliers directly affects the quality of the model. Iterative study involving different range of inclusions was too expensive to include in the study.

- Dequantization – this is the process of taking quantized weights which are frozen and not trained to be dequantized back to 32-bit precision. It is helpful to inference when quantized values and quantization constant can be used to backpropagate calculated gradients.

- Paged optimizers are necessary to manage memory usage during training of the language models. Azure NC4AS_T4_v3 family VMs handle this well but choice of sku is in initial decision not something that we can change during flight.

- BlobFuseV2 to load all the data stored in private storage accounts as local filesystem is incredibly slow for read over this entire dataset. Toolkit is more helpful to run on notebooks and laptops with VS Code, GPU, and customized local Windows Sub-system for Linux.

 

Drone Formation Commercial Software:

This is another addendum to the discussion about Drone Formation Commercial Software as described in this document. In this section, we describe surface detection. Drones can help with surface detection by using an encompassing space around the surface to detect. Let us assume that an object, say saucer like, needs to be surrounded by drones. The points of reference along the object as positions around which the drones must organize themselves can be easily determined by the drones themselves. We further assume each drone can detect the distance to the object by themselves or passed to each drone from some central sensor in real-time. If the scouts can figure out the overall matrix of X-Y-Z coordinates that is sufficiently large enough to encompass the object, then the rest of the drones can make use of this initial grid to spread themselves out before each converges to a point of reference closest to it and independent of others as long as it safe and not colliding with other drones.

The scouts do a two-pass flyover the objects while the distance remains within a range. If the range is exceeded, the boundary of the encompassing grid is known. With the initial pass determining the grid a subsequent pass to converge as close as possible to the surface of the object while maintaining a distance from each other, helps the drone to determine the points of reference on the surface. With the full strength of the drone formation then spreading over and distributing themselves against the points of reference will help to cover the surface.

When the number of drones is in excess of those required to cover the surface, they can form ever-increasing layers over the underlying points of reference. These points adjust to be on one layer for the layer above and the logic remains the same for additional drones to work out the new positions to occupy. Wave propagation and Fourier transform can predict how soon the drones can cover the object or the number of layers to form and the rate or duration for full coverage by all the drones.

Distance from each other as well as to a point of reference is sensor-specific and merely translates as an input for the drone to determine the actual position to occupy as the output. This works for all stationary objects in an outward-grid-enclosing-on-to-the-surface-of-the-object manner for the drone formation. Selection of the initial number and identities of the drones for the scouts can be determined by a threshold for the maximum distance allowed for a scout. After a scout reaches this threshold, another scout covers the next interval to the allowed threshold.

Moving objects have the characteristics that their shape remains constant during the flight and if the end points for the object can be tracked as distinct positions over time, then they the frame of reference can be adjusted to include the initial and final positions for a given time slice.

 This is the summary of the book titled “The art of explanation” written by Ros Atkins and published by Headline Publishing in 2023. He has been serving as the analysis editor for BBC and contends that the ability to explain oneself well is a necessary art. To be clear and concise at once takes practice and one that can be worked on each day. It applies to relationships. job interviews, email writing, teaching children and engaging any audience and is thus immeasurably useful and fortunately learnable. Crafting a quality explanation starts with the right questions, a certain degree of familiarity with the audience and their needs, preparations for controlled environments, and even training for impromptu speeches. Verbalizing and memorizing ahead of time helps. Anticipating and soliciting feedback helps to refine. Short form is preferable but the ability to delve in depth on demand is also important. Practice in everyday life can be transformational.

Explanation is a crucial skill that many people lack, as it can significantly impact how they communicate and achieve their goals. It is essential to convey our message clearly and impactfully, as it increases the likelihood of being understood and achieving desired results. For example, entrepreneurs can attract investors by explaining their business models, teachers can enhance students' enjoyment by explaining complex information, patients can stick to diet plans by explaining their benefits, and government agencies can gain interest in their services by explaining how to access them. To craft a quality explanation, we must start with the right questions and master the art of explanation by addressing 10 key features: simplicity, essential detail, complexity, efficiency, precision, context, no distractions, engaging, useful, and clarity of purpose.

To create a memorable and engaging message, it is essential to understand our audience and our needs. This involves understanding demographics, age ranges, and what they already know and want to know. Crafting a bespoke message that resonates with our audience and applying oour knowledge to engage them are essential. Lastly, communicating our credibility on the subject matter wraps it up.

Preparation is crucial for effective explanations. In controlled scenarios, these seven steps can be followed:

1. Set-up: Clarify our audience and the purpose of our explanation.

2. Find the information: Gather information, including a summary, questions, research areas, and subject matter.

3. Distill the information: Filter out irrelevant elements and evaluate the data.

4. Organize the information: Divide it into strands or sections, focusing on high-impact elements or stories.

5. Link the information: Write down our first draft to ensure smooth flow and authenticity.

6. Tighten the explanation: Eliminate unnecessary elements and practice thoroughly.

7. Deliver the explanation: Rehearse our explanation thoroughly, using methods like reading from a script, flashcards, or memorizing. Record ourselves running through the explanation and address any gaps.

In uncontrolled situations also, it is essential to prepare and communicate clearly. Following the same steps as for controlled settings, including setting up, finding information, and distilling the information helps. In dynamic settings, organizing our information differently, with no more than five strands. Each strand should have a primary point, three supporting facts, and relevant context. Verbalizing and memorizing our dynamic explanation ahead of time, using bridging phrases to connect different elements and communicating our desired information seamlessly will have a tremendous impact. Practicing using memory techniques, such as creating a "memory palace" to visualize different strands of our explanation aids our retention. Anticipating questions or feedback others might have regarding our explanation, as people tend to be predictable and pattern-based, helps cover our base. Preparing for the worst-case scenario by imagining how we might respond if the roles were reversed is good preparation. We could preemptively formulate answers and research the situation before giving our explanation. This will help us prepare for both what we hope will happen and what may surprise us.

We must prepare for short-form explanations in just a couple of minutes by asking ourselves three questions before an interaction that requires an explanation. This will help us include the right details and make the conversation more engaging. Even with short emails, it's good for everyone to understand our message clearly. We must make your first sentence and subject line engaging and use formatting to highlight important details. We must avoid adding recipients who don't need to be included in the conversation. The art of explanation is a multifaceted process that must be adaptable to our unique circumstances. It's like a home chef testing recipes from a cookbook, and we can choose which aspects of our life to transform first with the art of explanation.

Previous book summary: BookSummary107.docx

Summarizing Software: SummarizerCodeSnippets.docx  

 This is a continuation of articles on IaC shortcomings and resolutions. The following article describes how data scientists leveraging the cloud infrastructure tend to think about individual files and archives rather than filesystems. Most data used by data scientists to train their models either lives in a remote blob store, filestore or some form of data store such as structured and unstructured databases and virtual data warehouses. Distributed file systems in operating systems and intercompatibility protocols between heterogeneous operating systems such as Linux and Windows have long addressed the problem of viewing remote file systems as local paths via mounts and mapped drives, yet the diligence to setup and tear down entire filesystems on local compute instances and clusters is often ignored. 

Part of the reason for such limited use of files and archives has been the popularity of signed URIs for remote files that facilitate sharing on a file-by-file basis as well as the adoption of new file formats like parquet and zip archives for convenient data transfer. When changes are made to these files, they often require unpacking and packing and one-time update at the remote location. 

With the convenience of BlobFuse2 technology, mounted file systems can persist changes to remote location near instantaneously and are available for blob stores just as much as the technology is available for file stores. BlobFuse is a virtual system driver for Azure Blob Storage. It can be used to access existing blob data through the Linux File system. Page blobs are not supported. It uses libfuse open-source library to connect to the Linux FUSE kernel module. It implements filesystem operations by using Azure Storage REST APIs. Local file caching improves subsequent access times. An azure blob container on a remote Azure Data Lake Storage Gen 2 file system is mounted on Linux and its activities and resource usage can be monitored. The version 2 provides more management support through the Command-Line Interface  

On the other hand, the Azure File Storage offers fileshares in the cloud using the standard SMB protocol. Enterprise applications that rely on fileservers can find this transition easier. File shares can be mounted even from virtual machines running in Azure and on-premises applications that support SMB 3.0.

To mount the file share from a virtual machine running Linux, an SMB/CIFS client needs to be installed and if the distribution does not have a built-in client, it can be installed with the cifs-utils package. Then a mount command can be specified to make a mount point by giving the type, remote location, options, and local path as parameters. Mount shares can be persisted across reboots by adding a setting in the /etc/fstab file.

Lastly, as with all cloud resources and operations, all activities can be logged and monitored. They come with role-based access control for one-time setup and control plane operations can be automated with command-line interface, REST API calls, user-interface automations, and Software Development Kits in various languages.

Previous write-up: IaCResolutionsPart135.docx

 

Problem::

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 |